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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
