LEFT JOIN - CFOUTPUT Problem

Question

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 · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded