I have 5 tables which are

- Project(PK Project_ID), 
- Bottles (PK Bottle_ID, FK Project_ID), 
- Plastics (PK Plastic_ID, FK Project_ID), 
- Glasses (PK Glass_ID,FK Project_ID) and 
- Cups (PK Cup_ID, FK Project_ID)

How can I get one SQL query to return the number of Bottles ,Plastics , Glasses and Cup per Project, where One Project has many Bottles ,Plastics , Glasses and Cup ?

  • Is this a MySQL Question ??? – RolandoMySQLDBA Jun 16 '15 at 16:13
  • It is SQL server 2008 – Loic Jun 17 '15 at 17:55

Something like this will work, though is not necessarily efficient on all DB engines:

select Project_ID,
count(distinct Bottle_ID) bottlesCount,
count(distinct Plastic_ID) plasticsCount,
count(distinct Glass_ID) glassesCount,
count(distinct Cup_ID) cupsCount
from Project
left join Bottles using (Project_ID)
left join Plastics using (Project_ID)
left join Glasses using (Project_ID)
left join Cups using (Project_ID)
group by Project_ID;

Not the answer you're looking for? Browse other questions tagged or ask your own question.