Simple Join Help/Learning

[drath]

For some reason, I can handle every aspect of MYSQL, except JOIN/GROUP BY/LEFT JOIN - they just completely escape me - all the examples I look at don't seem relevant. So I thought a good way to learn would be using a real example that I would normally need to use one of them.

 

So far, I've been getting away with doing nested statements to get by, but I would like to optimize my site a bit better. So I ask you fine people, how would I optimize the following:

 

$sql = "SELECT gamename, user_id FROM highscores ORDER BY ID DESC LIMIT 5";

$result = mysql_query($sql) or die(sql_error($sql));

while ($row = mysql_fetch_array($result)) {

$gamename = $row['gamename'];

$user_id = $row['user_id'];

$sql2 = "SELECT username, ID FROM profiles WHERE ID = '".$user_id."'";

$result2 = mysql_query($sql2) or die(sql_error($sql2));

while ($row2 = mysql_fetch_array($result2)) {

$username = $row2['username'];

}

}

 

As you can see I'm using a nested SQL statement to compare user_id and ID from two different tables rather than using some of the more complex MYSQL statements - simply because I don't know how.

[kickstart]

Hi

 

Here you go

 

sql = "SELECT a.gamename, a.user_id, b.username FROM highscores a INNER JOIN profiles b ON a.userId = b.ID ORDER BY user_id DESC LIMIT 5
$result = mysql_query($sql) or die(sql_error($sql));
while ($row = mysql_fetch_array($result)) 
{
$gamename = $row['gamename'];
$user_id = $row['user_id'];
$username = $row['username'];
}

 

This assumes that ID is unique on the profiles table, and is always there.

 

If you want the details from highscores even if there is no matching record on profiles then use a LEFT OUTER JOIN.

 

If ID isn't unique on the profiles table then things would get more complicated. The above would bring but duplicates of the highscores records, one for each matching record on profiles.

 

All the best

 

Keith

[drath]

Wow, that means perfect sense to me now. Thanks a lot.

 

A kind of related question that just came up though: Using that same statement, is it possible to get a DISTINCT using only a certain field? In the case of this query, I want to make sure the a.gamename is DISTINCT. I noticed SELECT DISTINCT does not seem to work because it is taking all three selections to compare.

 

Also, do you think using your query over my original one would increase performance, as in, would it execute faster?

[kickstart]

Hi

 

It should be far more efficient than having nested queries.

 

As to DISTINCT, trouble is that you would need to specify which of the possible multiple records you want to return. You could use GROUP BY but it is a bodge (and would cause an error in most flavors of SQL) and will bring back a random row for the other fields.

 

All the best

 

Keith

[fenway]

Well, as long as whatever you group by is UNIQUE, you're ok -- at least in mysql.