Hello, thankyou to everyone who has helped me out on this one as I did
not think it was actuallay possible ! :)
This is what worked best for me :
SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username),
0) 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;
I'm sorry if I was not clear with my first email yesterday making it
sound like I wanted a 0 or a 1 and nothing else... I made a mistake and
thought that I was getting nothing or 1 whereas it was actually counting
corectly.
I was also suggested a LEFT OUTER JOIN but have read that it is a
synonym to LEFT JOIN, is this the case or is there a difference between
the two?
Thanks again,
Richard
David Schneider-Joseph a écrit :
Try this one:
SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username),
0) 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;
The LEFT JOIN will ensure you still get a result row even if there are
no matching rows in `login_table`. And the COALESCE will give you a
value of 0 instead of NULL for the count, in that case.
On Feb 19, 2008, at 5:29 PM, Richard wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
