|List:||General Discussion||« Previous MessageNext Message »|
|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 ?
|View as plain text|
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 >