Hey Mike
Can you post some results - it's not clear to me what you mean about
duplicate results for the _count columns.
Antony
On 12/12/06, Mike | NZSolutions Ltd <[EMAIL PROTECTED]> wrote:
>
> 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:263668
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
