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

[Peter Brawley]

Richard,

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

Do you mean by #1 that you want to list all users whether they have 
logged in or not? #2 is less clear still; does it mean the query is to 
show a count of 0 for no logins and 1 for any positive number of logins? 
If so, try...

SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) 
AS count
FROM user_list a
LEFT JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

PB

Richard wrote:
> 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
>>>
>>
>
>