Help with Join quiery

Discussion in 'MySQL' started by rjordan, Dec 8, 2012.

0
  1. #1
    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:
     
    rjordan, Dec 8, 2012 IP
  2. rjordan

    rjordan Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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]
     
    rjordan, Dec 9, 2012 IP
  3. aras

    aras Active Member

    Messages:
    533
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    60
    #3
    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
     
    aras, Dec 14, 2012 IP