SQL从多个表中选择随机，并按特定条件在一个表上排序

Question

我需要从3个表中选择一个随机记录，并确保我通过photoOrder

排序

Select TOP 1(a.id), a.mls_number, a.parcel_name, a.property_type, a.ownership_type,     b.filename, b.photoOrder, c.county_Name
From property as a
Inner JOIN
listingPhotos as b on a.id = b.ListingID 
LEFT JOIN
counties as C on a.county_name = c.id
WHERE a.isCommercial = 'True'
Order By NEWID()

所以这个查询工作，但我需要确保b.filename记录是由b.photoOrder排序的，因此b.photoOrder应该总是为1。b表(列出照片)每个属性有多个照片文件，我只需要选择照片顺序中第一个的照片。

谢谢

Answer 1

您可以子查询您的listingPhotos表并限制为WHERE PhotoOrder = 1:

Select TOP 1(a.id), a.mls_number, a.parcel_name, a.property_type, a.ownership_type,     b.filename, b.photoOrder, c.county_Name
From property as a
Inner JOIN
(SELECT ListingID , filename, PhotoOrder FROM listingPhotos WHERE PhotoORder = 1
       ) as b on a.id = b.ListingID 
LEFT JOIN
counties as C on a.county_name = c.id
WHERE a.isCommercial = 'True'
Order By NEWID()