How to SQL in many-to-many relationship

Question

I want to display how many hobbies does john have. Could you tell me how to write SQL statement?

PERSON table

ID | NAME
1 | John

HOBBY table

ID | NAME
1 | music
2 | sport

PERSON_HOBBY_COMBINATION table

ID | PERSON_ID | HOBBY_ID
1 | 1 | 1

expected result

HOBBY_NAME | HOBBY_EXIST
music | YES
sport | NO

Answer 1

If you already have John's ID and just want a raw count, then this should work.

select count(*) from person_hobby_combination where person_id=?

Answer 2

Select NAME, count(*) AS NoHobbies from Person p
inner join PERSON_HOBBY_COMBINATION phc
on p.ID = phc.PERSON_ID
group by p.ID, p.NAME

Note that you should group on both ID and NAME of the person.
You need to group on NAME because you have it in the output, but if you have duplicate names grouping on NAME will sum several persons hobbies together, that is why you need to group on ID too.

Edit
When inspection your expected result you don't want how many hobbies John has, but which hobbies. Then you need to write

Select p.NAME as PersonName, h.Name as HobbyName, case when phc.ID is null then 'No' else 'Yes' end as HasHobby from Person p
inner join Hobby h
on 1 = 1
left outer join dbo.PersonHobbyCombination phc
on p.ID = phc.PersonID and h.ID = phc.HobbyID

Answer 3

This might work for you:

SELECT h.name,  
CASE WHEN ph.id IS NULL THEN 'No' ELSE 'Yes' END AS hobby_exist
FROM hobby h 
CROSS JOIN person p 
LEFT JOIN person_hobby_conbination ph ON (ph.p_id = p.id AND ph.h_id = h.id) 
WHERE (p.name = 'John')