>If all your photos will have prices you won't need to left join you can
>just join on them.
>
>
>
>Joel Watson wrote:
>>
That's true, but when I combine it with the LEFT OUTER JOIN on the comments, it
screws up the comments. While it will return the prices information fine, it
duplicates the comments values for the number of price fields there are. For
example, each photo currently has 8 price values which are being returned.
With the query and output that I currently have, the photos with comments have
their comments values returned the number commens * 8. So if photo1 has 3
comments, it will return 24 values for the comments.
I am pasting below the current query that I have, as well as my current output
structure.
Thanks for everyone's help so far---it's almost there!
<cfquery name="rsPhotoInformation" datasource="#application.dsn#">
SELECT *
FROM photos
JOIN prices ON
photos.photoID = prices.photoID_fk
LEFT OUTER JOIN comments ON
photos.photoID = comments.photoID_fk
WHERE categoryID_fk = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
</cfquery>
<cfoutput query="rsPhotoInformation" group="photoID">
#photoTitle# <br />
<cfoutput group="priceID">
#priceValue# <br />
</cfoutput>
<cfoutput>
#commenter#
</cfoutput>
<br />
<br />
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284353
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
