From: Peter Brawley
Date: February 19 2008 5:09pm
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 ?
List-Archive: http://lists.mysql.com/mysql/211410
Message-Id: <47BB0D4F.30407@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>