J'ai deux tables, la première ressemble à ceci:
[Comparaisons]
Le deuxième:
[VehicleSpecs]
J'ai cette requête
SELECT TOP 100 * FROM [Comparsions] WHERE ORDER BY [TimeStamp]
Il renvoie les 100 loggings les plus récents, mais je dois replace Car_Id1
et Car_Id2
par les informations de la deuxième table comme ceci: Car_Id1 -> [Year + Make + Model]
Vous aurez donc besoin de deux INNER JOIN
VehicleSpecs
tableau VehicleSpecs
, un pour chaque Car_Id1
et Car_Id2
. Je les ai alias comme car1, car2
.
SELECT TOP 100 c.Id, c.Slug, c.TimeStamp, /* Select the relevant columns from *both* of the joined tables */ /* And give each column an alias to differentiate it from the other */ car1.Year AS car1Year, car1.Make AS car1Make, car1.Model AS car1Model, car2.Year AS car2Year, car2.Make AS car2Make, car2.Model AS car2Model FROM Comparisons c /* Join first against VehicleSpecs for Car_Id1 */ INNER JOIN VehicleSpecs car1 ON c.Car_Id1 = car1.Id /* Then once more for Car_Id2 */ INNER JOIN VehicleSpecs car2 ON c.Car_Id2 = car2.Id ORDER BY c.TimeStamp
Vous avez dit que vous vouliez le plus récent , donc je suppose que vous voulez réellement utiliser un ordre décroissant sur les horodatages:
ORDER BY c.TimeStamp DESC
Joignez-vous à la deuxième table deux fois:
select top 100 c.Id, c.Slug, c.TimeStamp, s1.Year as Car1_Year, s1.Make as Car1_Make, s1.Model as Car1_Model, s2.Year as Car2_Year, s2.Make as Car2_Make, s2.Model as Car2_Model from Comparsions c inner join VehicleSpecs s1 on s1.Id = c.Car_Id1 inner join VehicleSpecs s2 on s2.Id = c.Car_Id2 order by c.TimeStamp desc
(Remarque: vous pouvez vouloir corriger le nom de la table en Comparisons
.)