Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

[Richard]

Sorry it's me again, I made a mistake, it counts the number of logins 
correctly, but does not show members with 0 logins !

Any idea how to do this?

Thanks :)

Peter Brawley a écrit :
> Richard,
> 
>  >Can I do something like this :
>  >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
>  >FROM login_table b WHERE a.username = b.username) FROM user_list a
> 
> Try ...
> 
> SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
> FROM user_list a
> JOIN login_table b ON a.username = b.username
> GROUP BY a.username,a.first_name,a.lastname;
> 
> PB
> 
> -----
> 
> Richard wrote:
>> Hello,
>>
>> This time I'm rearly not sure if this is possible to do. I've got two 
>> queries that I would like to bring together to make only one query ...
>>
>> I've got a list of users
>>
>> And also a login table
>>
>> I would like to list all users and show the number of times they have 
>> logged in.
>>
>> So to get the list of users I would do :
>>
>> SELECT username, first_name, last_name FROM user_list
>>
>> And to count the number of connections I would do
>>
>> SELECT COUNT(*) AS count FROM login_table WHERE username = 
>> $result['username']
>>
>> Can I do something like this :
>>
>> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
>> count FROM login_table b WHERE a.username = b.username) FROM user_list a
>>
>> I know that the above query can not work but It's just to give a 
>> better idea about what I'm trying to do . :)
>>
>> If I do a join, I will the username repeated for each login.
>>
>> Thanks in advance,
>>
>> Richard
>>
>