distinct and join error

[techlover]

Hi,

I am trying to pull some data from 2 tables without getting duplicates. Each table has a field called user.

table1 has user and score
tabe2 has user and name

I am try to pull the 3 best scores from table1 without duping. IE if user1 has top score, he is done.

Here is my code:

select distinct(table1.username),table1.score,table2.username from table1 LEFT JOIN username ON table1.uid=table2.uid where table1.blah='whatever' order by table1.score DESC limit 3

I keep getting duplicate users. I tried an inner join I found here but got same results.

any help is appeciated

[nozom]

off the top of the old noodle:

this is assuming that 'user' is an integer ID value and is used to identfy each user

$sql=mysql_query("SELECT * FROM table1,table2 WHERE table1.user=table2.user");

while ($row=mysql_fetch_array($result)
   {
   echo "username is:". $row[name] ." - score is:". $row[score]."<br><br>";

   }

hope this helps.
nozom

[techlover]

the problem is getting the high score.

Table 1 has entries like this

username score

bob 81
bob 787
bob 7
mary 82
jean 837
gogo 78
bob 71

So I would need say the 3 highest scores BUT unique usernames. So bob could only be 1,2 or 3.

Result from above example would be:

jean 837
bob 787
mary 82

[nozom]

so you are looking to pull out the highest score for each individual? not the highest scores, no matter who got the scores.

There is probably a better solution to this but here goes.

table1 has user and score
tabe2 has user and name

$sql=mysql_query("SELECT * FROM table2 ORDER BY user"); 
while ($row=mysql_fetch_array($result) 
   {
   $currentuser=$row[user];
   $sql2=mysql_query("SELECT * FROM table1 WHERE user=$currentuser ORDER BY score DESC LIMIT 1 "); 
   while ($row2=mysql_fetch_array($result2) 
      {
      $topscore=$row2[score];
      }
   echo $row[name]." ".$topscore."<br><br>";
   }

   echo "username is:". $row[name] ." - score is:". $row[score]."<br><br>"; 

   }

hope that helps,
nozom

[tomhath]

I didn't test this:

select  table2.username,  max(table1.score)
from table1 
LEFT JOIN table2
ON table1.uid=table2.uid 
where table1.blah='whatever' 
group by  table2.username 
order by 2 DESC limit 2