SQL statement to select rows from table 1 and count rows from table 2

[pmonte]

I have two tables, one for users and the other one for messages.

User contains: id, name, address, etc etc
Message contains: id, userid, status, etc etc

How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid )?

One step forward:
How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid ) with Message.status = Unread?

[deepuv04]

I have two tables, one for users and the other one for messages.

User contains: id, name, address, etc etc
Message contains: id, userid, status, etc etc

How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid )?

One step forward:
How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid ) with Message.status = Unread?

select user.id,user.na me,count(messag es.id)
from user inner join
message on message.userid = user.id
group by user.id,user.na me

this query gives you the list for all users. to get the results for a particular user or on a specific condition use where clause or having clause

[pmonte]

select user.id,user.na me,count(messag es.id)
from user inner join
message on message.userid = user.id
group by user.id,user.na me

this query gives you the list for all users. to get the results for a particular user or on a specific condition use where clause or having clause

thanks a lot! It almost worked as I need. The only point is that:
if a user has no messages this is not included in the resulting table while I'd need also the user with no messages in it (with count = 0).

[deepuv04]

thanks a lot! It almost worked as I need. The only point is that:
if a user has no messages this is not included in the resulting table while I'd need also the user with no messages in it (with count = 0).

in the query given replace "INNER JOIN" with "LEFT OUTER JOIN" this will return the users without messages.

[pmonte]

in the query given replace "INNER JOIN" with "LEFT OUTER JOIN" this will return the users without messages.

so one or the other, cannot generate a table with both (user with AND without messages)
Basically my goal is to "add a coloum" to user table with the number of messages received by each user

[deepuv04]

Try this

select user.id,user.name,
count(messages.id) as Messages,
COUNT( case when message.Status ='Unread' then 1 else null) as UnreadMessages
from user LEFT OTUER JOIN
message on message.userid = user.id
group by user.id,user.name

[pmonte]

Try this

select user.id,user.name,
count(messages.id) as Messages,
COUNT( case when message.Status ='Unread' then 1 else null) as UnreadMessages
from user LEFT OTUER JOIN
message on message.userid = user.id
group by user.id,user.name

as far as I can understand this count the read and unread messages but I'd need this:
User table has two users, id=1 (name= paul) and id=2 (name=john). Message table has three messages all of them with userid=1.
The results should be:
Paul 3
John 0

[pmonte]

in the query given replace "INNER JOIN" with "LEFT OUTER JOIN" this will return the users without messages.

left outer join worked exactly as I need, sorry for the confusion and thanks a lot for your help!