1 more left join problem :(

[miz]

Originally Posted by Dean C

You'll need to do the count in a seperate query AFAIK

Btw can you show the query you used in the end to get the sessions working ?

this is the query

PHP Code:

$teamusers =  $DB_site->query("SELECT user.*  AS user
       FROM " . TABLE_PREFIX . "user 
       LEFT JOIN " . TABLE_PREFIX . "session
        ON(session.userid = user.userid)
       WHERE user.teamid='$teamid' 
      GROUP BY user.userid ORDER BY session.lastactivity DESC
       "); 


its also helped me in other query
i run about 100 tests lol and i surely can say that i understand
left joins better
now i want to run count inside the left join
and i need to update counters for each team :\
sucks

Originally Posted by Dean C

You need to put parentheses around the asterix sabe :

[sql]
SELECT
COUNT(*) as total
FROM
user
[/sql]

but i dont want to count rows

i want to do like

user.posts =5 (userid=1)
user.posts=6 (userid=5)

total teams posts = 6
can i do query for that?

never mind i fixed it
this is the query

[SQL]
$teamslist = $DB_site->query("
SELECT teams.*,user.username AS username, user.userid AS userid ,user.posts AS userposts, user.reputation AS userrep,COUNT(user.posts) AS totalteamposts
FROM " . TABLE_PREFIX . "teams AS teams
LEFT JOIN user ON(teams.teamid = user.teamid)
WHERE teams.teamid > 1
GROUP BY teams.teamid
");
[/SQL]