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]

Hi, and thankyou for trying to help me out! I've tried this and it does 
not work. Here are the problems :

1) If a user has never logged in he doesn't show the user in the list
2) It doesn't count if it is  0 it's not on the liste and if the user 
has logged in more than once the result is 1 (because of the group by ...).

Thankyou

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
>>
>