Help with Join quiery

Hi guys ,

I am new here and have a question about joun quiery. I will aprechiate any help. Here is my table structure:

[TABLE="class: grid, width: 500"]
[TR]
[TD="align: center"]USER
[/TD]
[TD="align: center"]SERVICE
[/TD]
[/TR]
[TR]
[TD]userid PK[/TD]
[TD]serviceid PK[/TD]
[/TR]
[TR]
[TD]username[/TD]
[TD]userid FK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]servicename[/TD]
[/TR]
[/TABLE]


[TABLE="class: grid, width: 500"]
[TR]
[TD]USER DATA
[/TD]
[TD]SERVICE DATA
[/TD]
[/TR]
[/TABLE]
[TABLE="class: grid, width: 500"]
[TR]
[TD]userid
[/TD]
[TD]username
[/TD]
[TD]serviceid
[/TD]
[TD]userid
[/TD]
[TD]servicename
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]user1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]someservice[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]user2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]someservice[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]someservice[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]someservice[/TD]
[/TR]
[/TABLE]


I Want to display total number of services per user

[TABLE="width: 500"]
[TR]
[TD]username[/TD]
[TD]total services[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]2[/TD]
[/TR]
[/TABLE]






I am trying to use somethign lke this:


SELECT user.username, (SELECT COUNT(service.userid)) FROM user, service GROUP by username


but it doesn't return the total servces per user, it only returns same totals for all users. like:

 

I managed to make it work , here is the query:

SELECT DISTINCT user.username, service.serviceice FROM user
INNER JOIN service ON user.userid = service.userid
GROUP BY user.username
ORDER BY user.username



No what i want to get is if the use r has no any service, to return the user account with 0 services value. How i can do that:


[TABLE="class: cms_table, width: 500"]
[TR]
[TD]username[/TD]
[TD]total services[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]user2
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]user3
[/TD]
[TD]0 (has no service)
[/TD]
[/TR]
[/TABLE]

 

SELECT u.user_name,s.count FROM user u
LEFT OUTER JOIN (SELECT user_id,COUNT(serviceid) count FROM service GROUP BY user_id) s ON s.user_id=u.user_id


count should return NULL for users with no services.
if you do inner instead of left join then those are filtered and not displayed on results