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

I'm afraid we don't do homework here at dbforums but I suggest you into outer joins.

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

The first example on this page should translate exactly to what you are after.

Thanks Mike.

Much appreciated