CODEWITHSUNDEEP
sqlselectjoincount
MDP
MDP

Reputation: 4267

Select with count on 3 tables

I need your help for a particular SELECT on 3 tables. I'm not skilled on SQL so it's a difficult SELECT for me, since I have to apply COUNT (I suppose) to the query.

I show you my tables:enter image description here

I need to know how many contacts there are in the database (all the contacts!!!!) and how many photos and videos are bound to any contact.

I should get a result similar to this:

-----------------------------------
| ID | NAME       | PHOTO | VIDEO |
-----------------------------------
| 1  | MARK       | 3     | 1     |
-----------------------------------
| ID | LISA       | 2     | 0     |
-----------------------------------

Thank you for your help

Upvotes: 0

Views: 1105

Answers (2)

Gulam Husain Ansari
Gulam Husain Ansari

Reputation: 165

Please use below query , this will give you exact result

select contact_media.ID_Contact, contact.Name, count(M1.ID) as 'PHOTO', COUNT(M2.ID) as 'VIDEO' from Contact inner join contact_media on Contact.ID=contact_media.ID_Contact
left outer join media M1 on contact_media.ID_Media=M1.ID and M1.TYPE='PHOTO'
left outer join media M2 on contact_media.ID_Media=M2.ID and M2.TYPE='VIDEO'
 group by contact_media.ID_Contact, contact.Name

Upvotes: 1

Irdis
Irdis

Reputation: 986

You can use the following approach, if you are hesitant about duplicates in the query you can use a sql function and pass type parameter as a string. If you have uncertain number of types (VIDEO, PHOTO, TEXT etc) you need to redesign the output table format (I would go with the following tuple TYPE, CONTACT_ID, COUNT), or at the worst case go with dynamic query construction.

select c.ID, c.NAME, 
(select count(*) from CONTACT_MEDIA cm join MEDIA m on 
m.ID = cm.ID_MEDIA and m.TYPE = 'PHOTO' where cm.ID_CONTACT = c.ID) as PHOTO, 
(select count(*) from CONTACT_MEDIA cm join MEDIA m on 
m.ID = cm.ID_MEDIA and m.TYPE = 'VIDEO' where cm.ID_CONTACT = c.ID) as VIDEO
from CONTACT c

Upvotes: 1

Related Questions