really stuck on this if anyone is able to help!!
(sorry for the desparation)
>Hi guys,
>
>I have the following tables...
>
>Media, media_image, media_file
>
>The relationship between the media table and the other two is a
>one-to-many relationship. I wish to query the media table and show a
>count of any associated images/files. What I have so far is...
>
>(NOTE: using MSACCESS)
>
><cfquery name="getMedia" datasource="#Request.App.dsn#"
>password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
>SELECT media.media_id, media.dCreated, media.dModified,
>media.media_title, media.media_description, media.enclosure,
>COUNT(media_image.media_image_id) AS image_count,
>COUNT(media_file.media_file_id) AS file_count
>FROM (media LEFT OUTER JOIN media_image ON media.media_id =
>media_image.media_id)
>LEFT OUTER JOIN media_file ON media.media_id = media_file.media_id
>WHERE 1=1
><cfif isDefined("arguments.media_id")>
>AND media.media_id = <cfqueryparam cfsqltype="cf_sql_integer"
>value="#arguments.media_id#">
></cfif>
>GROUP BY media.media_id, media.dCreated, media.dModified,
>media.media_title, media.media_description, media.enclosure
>ORDER BY media.dCreated DESC
></cfquery>
>
>At moment it is giving duplicate results for image_count and file_count.
>
>Can anyone see what I am doing wrong?
>
>Thanks heaps!
>
>mike
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:263664
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
