Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Select count on table2 based on a select from table1

    Hi everyone.

    Apologies if this is posted on the wrong forum. Here is what I am trying to do

    I have 2 tables in Sybase I want to query

    I have a list of users in table1

    If select distinct users from table1 i then want to query table2 for the count of rows for each user. If a user has no rows in table2 I still want to output 0 as a result.

    This is what I tried

    select distinct users, count(*) as amount from table2 where users in (select distinct users from table1 )group by users

    users amount
    ------------------------------ -----------
    Bill 2
    Simon 48
    Gill 866
    Imogen 2978


    This is ok but there is a user called Matilda in table1 with now rows in table 2 and I want to show something like

    users amount
    ------------------------------ -----------
    Bill 2
    Simon 48
    Gill 866
    Imogen 2978
    Matilda 0

    Again I am really sorry If I have posted in the wrong forum.

    Thanks for your time

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm afraid we don't do homework here at dbforums but I suggest you into outer joins.

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Mike

    Thanks for your time,

    Unfortunatley the last time I did homework was 18 years ago when I was at school. This old fart is only trying to learn a bit of SQL to help with a work problem. I thought I was doing something silly.
    I admit the table names seemed a bit year 1 I was just trying to make the post as basic as possible so I renamed my tables

    But again thanks for your suggestion

    Kind Regards
    Last edited by sharkspm; 03-10-10 at 16:44.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The first example on this page should translate exactly to what you are after.

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Thanks Mike.

    Much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •