Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname;
Donna Richard <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
