Need help converting nested queries into JOIN query

[schwim]

Hi there everyone,

I'm reading up on tutorials to try to figure out how to streamline my code, but I'm really having problems figuring out JOINs. I've got a very inefficient looped query:

$query3 = "SELECT * FROM addon_notify WHERE gachiev = '1'";
$result3 = mysql_query ($query3) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query3);
$result_rows3 = mysql_num_rows($result3);
if($result_rows3 > '0'){
	// Somebody needs to be told about this.
	while ($row3 = mysql_fetch_assoc ($result3)) {
		$fid = $row3['uid'];
		$query4 = "SELECT email FROM php_users WHERE user_id = '$fid'";
		$result4 = mysql_query ($query4) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query4);
		$result_rows4 = mysql_num_rows($result4);
		if($result_rows4 > '0'){
			// Somebody needs to be told about this.
			while ($row4 = mysql_fetch_assoc ($result4)) {
				$email = $row4['email'];
			}
		}
	}
}

Basically, I'm trying to get the email where user_id in phpbb_users = the uid in addon_notify but only when gachiev is '1' for that uid.

Could someone help me with how to convert this to a single join?

Thanks in advance for any help you can provide!

[jeepin81]

If I am understanding your query correctly, you should be able to JOIN them like the following and get your results.

$sql = "SELECT * FROM php_users LEFT JOIN addon_notify ON user_id = uid AND gachiev = 1";

[schwim]

Thanks very much for your help, jeepin.

I've tried to implement your suggestion, but I have messed up some how. My database should have returned a single result, as there's only one record in addon_notify with gachiev set as '1'. My goal was to have the addon_notify being of primary importance and using phpbb_users only to get the email address for the uid found in addon_notify. Instead, I got a result for every record in phpbb_users, whether or not they had a record in addon_notify with gachiev being '1'. Here's the code I used:

$query3 = "SELECT * FROM phpbb_users LEFT JOIN addon_notify ON user_id = uid AND gachiev = 1";  

$result3 = mysql_query ($query3) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query3);
$result_rows3 = mysql_num_rows($result3);
if($result_rows3 > '0'){
	// Somebody needs to be told about this.
	while ($row3 = mysql_fetch_assoc ($result3)) {
		$fid = $row3['uid'];
		$email = $row3['user_email'];
		echo("fid is ".$fid." and email is ".$email.".");
	}
}

Your thoughts would be very welcome.

[Weedpacket]

One thing that will make things clearer is to identify which columns you're selecting, and which tables they are coming from. (Note: in what follows I'm only guessing at the latter). This would also improve the query optimiser's operation.

Using a LEFT JOIN will retrieve rows in the table on the left whether or not there are any matching rows on the right.

Select
    addon._notify.uid,
    phpbb_users.user_email
From
    phpbb_users, addon_notify
Where
    phpbb_users.user_id = addon_notify.uid
    AND
    addon_notify.gachiev = 1