CODEWITHSUNDEEP
sql-servert-sql
Dashsa
Dashsa

Reputation: 718

SQL Select random from multiple table and order by specific criteria on one table

I need to select a random record from 3 tables and ensure I am ordering by 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()

So this query works, but I need to ensure that the b.filename record is ordered by b.photoOrder and thus the b.photoOrder should always be 1. The b table (listing photos) has multiple photo files per property and I need to only select the photo that is 1st in the photo order.

Thanks

Upvotes: 1

Views: 92

Answers (1)

Hart CO
Hart CO

Reputation: 34784

You could subquery your listingPhotos table and limit to 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()

Upvotes: 3

Related Questions