|List:||General Discussion||« Previous MessageNext Message »|
|From:||Richard||Date:||February 19 2008 10:15pm|
|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|
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 >> >