1 more left join problem :(

[miz]

ok i tried to run this query
[SQL]
$teamusers = $DB_site->query("SELECT * FROM " . TABLE_PREFIX . "user
LEFT JOIN " . TABLE_PREFIX . "session ON(session.userid = user.userid) WHERE user.teamid='$teamid' ");
[/SQL]

this query should get all users which in $teamid and their online status

now query works fine but
if userid show up few times in sessions
then its show up the user few times
but i want it to show each user only 1 time

what am i doing worng ?

thanx for help

[Andreas]

This is tricky ...

Which information from the session table do you need?
Because if you GROUP BY session.userid, it will just take one session for each user, but not necessarily the active one

[miz]

hmmm
this is what im tring to do

PHP Code:

$datecut = TIMENOW - $vboptions['cookietimeout'];
 $teamusers =  $DB_site->query("SELECT * FROM " . TABLE_PREFIX . "user 
      LEFT JOIN " . TABLE_PREFIX . "session  ON(session.userid = user.userid) WHERE user.teamid='$teamid'  ");
 while ($members = $DB_site->fetch_array($teamusers))
     {
 $useronline =    fetch_online_status($members);
 } 


now basiclly this query should give me
list of users which in $teamid and their online status

but its get ++++ed up some where

[Dean C]

Change:

SELECT *

to

SELECT DISTINCT session.userid, user.username

[miz]

Originally Posted by Dean C

Change:

SELECT *

to

SELECT DISTINCT session.userid, user.username

ok i tested it
and its show me only 1 time the user
but its also give me worng online status


btw what the word DISTINCT means ?

EDIT :
=====
for online status you should get also session.lastactivity
i tired add it and its again gave me 2 user names

[Dean C]

DISTINCT means you only return unique rows. Ummm try:

[sql]
SELECT
*
FROM
" . TABLE_PREFIX . "user
LEFT JOIN
" . TABLE_PREFIX . "session ON(session.userid = user.userid)
WHERE
user.teamid='$teamid'
GROUP BY
userid
ORDER BY
session.lastactivity DESC
[/sql]
[/sql]

[Andreas]

As I already said above, GROUP BY will not necessarily catch the last session ...

[sabret00the]

deans would probably work if you select distinct and remove the group by as it's getting the last session to check the timeout it should return the wanted information.

[miz]

THANK YOU VERY MUCH
you guys have been replly helpfull to me

now please 1 more qustion

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

now i want it to take all userposts and count them togeder
is that possible ?

[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 ?