COUNT FROM MULTIPLE TABLES

[Silvinho]

I have a database that has 1 table(Images) that store all my uploaded image files.

there are then 3 other tables that store some of these image files as one of the fields

The problem im having is i want a page that lists all the images from Table 1(Images) with a total count of the times the image is used from the 3 other tables.

This problem just seems to be getting me more and more confussed, can anyone help???

 

[Tarwn]

Select Table1.imageName, Count(Table2.image) as cnt2, Count(Table3.image) as cnt3, Count(Table4.img) as cnt4 FROM Table1, Table2, Table3, Table4 
WHERE Table1.image_id = Table2.image_id AND Table1.image_id = Table3.image_id AND Table1.image_id = Table4.image_id 
GROUP BY Table1.image_name

The only problem with this is it will only return records where there is at least 1 entry for a specific image in all 4 tables.

-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[mwolf00]

The way to overcome that problem would be with outer joins... Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[Tarwn]

Oh, but thats to easy ;-) ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924