Sorting query output

[doodlebug]

Hi Guys,

First of all, here is the query i am running:

[code=php:0]
$query = "select * from plmatchdata where lid='$lid' and matchdate>='$todaysdate' and week='$ww' order by matchdate";
$result = $dbase->query($query);
while ($l = mysql_fetch_array($result)) {

      $matchid = $l["matchid"];
      $nextmatch = $matchid;

$querystand="SELECT userid FROM pluserlids WHERE lid='$lid' AND userid NOT IN (SELECT DISTINCT userid FROM plpredictiondata WHERE matchid='$nextmatch')";
$resultstand = mysql_query($querystand,$db);
while ($latest_array = mysql_fetch_array($resultstand)) {

      $user= $latest_array["userid"];

        $query2 = "SELECT email FROM pluserdata WHERE userid='$user'";
        $res2 = mysql_query($query2,$db);
        $l2 = mysql_fetch_array($res2);
        $name2 = $l2["email"];

        $query3 = "SELECT username FROM pluserdata WHERE userid='$user'";
        $res3 = mysql_query($query3,$db);
        $l3 = mysql_fetch_array($res3);
        $name3 = $l3["username"];

print("<tr><td class='TBLROW'><font class='TBLROW'>$name3</font></td><td class='TBLROW'><font class='TBLROW'>$user</font></td><td class='TBLROW'><font class='TBLROW'>$name2</font></td></tr></font>");
}
}
[/code]

Based on the structure of the query as it stands at the moment, it is returning the correct results. I am getting a list of userids, usernames and emails where the criteria is met. The problem i have, is that it is returning a list of names, emails etc.. for every criteria met in the first part of the query aswell, so i am getting duplicates.

What i would like to know is how to structure the query so it will only return a username etc.. once if it comes up anywhere in the second part of the query.......but i'm stuck  :-(

Hope some one you knowledgable chaps can help me out :-)

Best regards
Doodlebug

[fenway]

Why can't you do this all in a single pass?

[doodlebug]

[quote]Why can't you do this all in a single pass?[/quote]

'cos i don't know how to. I don't fully understand joins  in queries yet. I am looking at them, but i haven't got my head around them. Any help you could give would be greatly appreciated. ;)

[btherl]

It looks to me like you want...

[code]SELECT DISTINCT username, userid, email
    FROM plmatchdata
    JOIN plpredictiondata USING (matchid, lid)
    JOIN pluserlids USING (userid)
    JOIN pluserdata USING (userid)
    WHERE lid = '$lid'
    AND matchdate >= '$todaysdate'
    AND week = '$ww'
    ORDER BY matchdate[/code]

Essentially, the USING (userid) means "Join these tables where userid matches).

First you join plmatchdata and plpredictiondata where the matchid column matches.
Then you join with pluserlids and pluserdata where the userid matches.

The "DISTINCT" removes duplicates, if there are any.

If that query doesn't work, post the error message here (and if possible, post the schema for your tables.  I've had to guess some parts of the query because I don't know what columns are where, and am not 100% sure which ones you want to match)

[fenway]

That's basically the idea, though I personally prefer an explicit ON clause, and using GROUP BY instead of DISTINCT.