dimanche 10 juillet 2016

MySQL pivot table counting

I've three tables:

users
------
user_id
name

subjects
--------
subject_id
name

pivot_user_subject
------------------
user_id
subject_id

I've this query to show subjects and usernames:

SELECT a.name User, c.name Subject 
FROM users a, pivot_user_subject b, subjects c 
WHERE a.user_id = b.user_id AND c.subject_id = b.subject_id;

and works great. but now I need to show how many subjects an user has.

Example output:

User      Subjects                      Subjects_count
Juan      Math, Biology, Spanish               3
Rosa      NULL                                 0
Rodrigo   Math                                 1

Any idea about how can I accomplish that output only with SQL queries? Thanks.

Aucun commentaire:

Enregistrer un commentaire