LEFT JOIN - CFOUTPUT Problem

[Guest]

Ok guys, another one......

I have two tables, "listings" and "photos"......each listing could have 0, 1, or more photos associated with it...

Listings is setup like ListingID | ListingPhoto
Photos are setup like PhotoID | PhotoListing

I'm simply trying to output a page with all listings...under each listing would output all associated photos.....so in the below example, with 1 listing and 4 associated photos, I should have 1 row with the listing info, and 4 photos below it......I"m using:

SELECT * FROM Listings LEFT JOIN ListingPhotos ON Listings.ListingID = ListingPhotos.PhotoListingID ORDER BY ListingDateAdded

Now, there is 1 listing in the Listing table, and 4 photos in the photos table (all with the listingID of the 1 listing, so all are associated). When I run this, it outputs the same (only) listing 4 times....one for each photo, and one photo for each copy of the listing....what am I doing wrong? I've used this similar query for joins before with no issues.....

[paross1]

You could do this in your output using a GROUP attribute in the cfoutput tag, except you would want to order by listingID first in your query, then use group = listingID in your cfoutput.

Something like this...

<cfoutput query="yourquery" group="ListingID">
#ListingID#
<cfoutput>
#PhotoID# #PhotoListing#
</cfoutput>
</cfoutput>

Also, does using DISTINCT in your SELECT make any difference?

Phil

[Guest]

hehe, that was it, exactly.....I didn't have the output page of my last project in front of me, and of course, it was grouping in the query output that did the trick, works great, thanks for the reminder!! 🙂