that's awesome jim, thanks heaps for your concise response - i actually understand what is going on now!
cheers mike >Mike, >What you have to remember with your query is that the COUNT is just a >count of rows...and that is done AFTER the data has been joined >together. If you look at how your joins are constructed, you will see >that your counts are going to be the product of the two joins... > >You can strip all of the extraneous stuff out of your query and have... > >SELECT media.media_id, >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 >GROUP BY media.media_id > >And let's say the data you have is as follows... > >*media* >media_id >1 > > >*media_file* >media_id media_fileid >1 10 >1 20 > > >*media_image* >media_id media_image_id >1 70 >1 80 >1 90 > > >If you run the above query, first it is going to join media to >media_image...and you have the results... >1 10 >1 20 > >Then it is going to join media_file with the results you returned from >the first join.... >1 10 70 >1 10 80 >1 10 90 >1 20 70 >1 20 80 >1 20 90 > >As you can see, every row of that third table is joined to anything that >matches in the first join, resulting in quite a few more rows than you >intended. So when you wrap your COUNT() around that column, it just >counts up the rows and spits out a number you don't want.... >1 6 6 > >There are DBs that support a COUNT(DISTINCT column), but I don't believe >Access is one of them. Your best bet is probably to do this through a >subquery in your SELECT statement... > >In the following example, I took out the second join, and replaced it >with a subquery to find the file_count...you could replace both the >joins with subqueries if you want. > >SELECT media.media_id, >COUNT(media_image.media_image_id) AS image_count, >(SELECT COUNT(media_file_id) FROM media_file WHERE media_id = >media.media_id) AS file_count >FROM (media LEFT OUTER JOIN media_image ON media.media_id = >media_image.media_id) >GROUP BY media.media_id > >-jim ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:263684 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
