SQL Server 2005 Join Table on pk and displayorder field

Hi,
I have two tables Listing and ListingPictures

What I would like to do is query these tables for listing data plus the listing picture with the lowest display order. Fields and sample data below:

Listing Fields:
ListingID | StreetAddress | City | State | Zip | etc

ListingPictures Fields
ListingPictureID | ListingID | FileName | DisplayOrder

Sample Listing Data
17 | 12345 Some Street West | Miami | Florida | 33161
18 | 23456 Some Street East | West Palm Beach | Florida | 33151

Sample ListingPictures Data:
29 | 17 | ListingPictures/12345.jpg | 1
30 | 17 | ListingPictures/23456.jpg | 2
31 | 18 | ListingPictures/34567.jpg | 1
32 | 18 | ListingPictures/45678.jpg | 2

So the ideal query for me would return results like below
ListingID | ListingPictureID | ListingPictureFileName      | StreetAddress | City        | State        | Zip
17          | 29                      | ListingPictures/12345.jpg | The Address    | The City | The State | The Zip
18          | 31                      | ListingPictures/34567.jpg | The Address    | The City | The State | The Zip

If anyone could help with that query I would greatly appreciate it.
Thanks in advance!
amagondesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
darkmooinkConnect With a Mentor Commented:
as for the not displaying of the record if there isnt a record on listingPicture you could add " or ListingPicture.DisplayOrder is null" to the end of the query that seems to work for some data i have.
but streamlining it i dont think i can help. i have tryed a few things but i eather dont get the results i want or i error.
0
 
darkmooinkCommented:
select * from Listing join ListingPictures on ListingPictures.ListingID = Listing.ListingID where DisplayOrder= 1
0
 
amagondesAuthor Commented:
Hi darkmooink,
thanks for the reply. the problem is that there is not always guaranteed to be a display order of 1. would it be possible to do something like:

SELECT Listing.*, ListingPicture.ListingPictureID, ListingPicture.FileName FROM Listing
LEFT JOIN ListingPicture ON ListingPicture.ListingID = Listing.ListingID
WHERE ListingPicture.DisplayOrder = (SELECT MIN(ListingPicture.DisplayOrder) FROM ListingPictures WHERE ListingPicture.ListingID = Listing.ListingID  )

This seems to work, but is there a more efficient way to write this query?

0
 
amagondesAuthor Commented:
Actually, if there are no pictures for a listing the listing won't get returned at all. i would like it to return listings whether they have a picture(s) or not, but if they do have a picture then return the picture with the lowest display order. thanks for the help
0
 
amagondesAuthor Commented:
Thanks for your help
0
All Courses

From novice to tech pro — start learning today.