Need help converting nested loops to subquery

[schwim]

Hi there everyone,

With the help of brad and a couple others, I learned that the way I was handling nested queries was very inefficient and was instructed on the wonders of subqueries. I'm quickly learning however that there's many differrent ways to build them and I'm not sure how to build them in all their forms.

This is the latest that I'm trying to convert:

				// Get last five guild joins.
				$query2 = "SELECT fid,name,level,gender,race,class,tnURL FROM addon_guild_members WHERE active = '1' ORDER BY join_time DESC LIMIT 5";
				$result2 = mysql_query ($query2) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query2);
				$result_rows2 = mysql_num_rows($result2);
				if($result_rows2>=1){
					while ($row2 = mysql_fetch_assoc ($result2)) {
						$fid = $row2['fid'];
						if($fid != '0'){
							$query3 = "SELECT username FROM phpbb_users WHERE user_id = '$fid'";
							$result3 = mysql_query ($query3) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query3);
							$result_rows3 = mysql_num_rows($result3);
							if($result_rows3>=1){
								while ($row3 = mysql_fetch_assoc ($result3)) {
									$fname = $row3['username'];
								}
							}
						}
						$name = $row2['name'];
						$level = $row2['level'];
						$gender = $row2['gender'];
						$race = $row2['race'];
						$class = class_text($row2['class']);
						if($level <= '10'){
							$tnURL = "http://us.battle.net//wow/static/images/2d/avatar/".$race."-".$gender.".jpg";
						}else{
							$tnURL = $row2['tnURL'];
						}

So, if $fid isn't 0, I need to find the username in php_users where user_id = $fid. I'm having problems on finding out how to build this query and any help would be greatly appreciated!

[laserlight]

In this case I would use a join, e.g.,

SELECT fid, username, name, level, gender, race, class, tnURL
    FROM addon_guild_members
        LEFT JOIN phpbb_users ON addon_guild_members.fid=phpbb_users.user_id
    WHERE active = '1' ORDER BY join_time DESC LIMIT 5

[schwim]

Thank you very much for your help laserlight,

With rare exception, the fid will be 0, which means it won't find a user_id to match up with in phpbb_users. Would it save any measurable resources to write a query that only joins the two queries when $fid doeesn't equal 0?

[laserlight]

schwim wrote:

With rare exception, the fid will be 0, which means it won't find a user_id to match up with in phpbb_users. Would it save any measurable resources to write a query that only joins the two queries when $fid doeesn't equal 0?

That's why my example does a left join instead of an inner join.

[johanafm]

Except that if he only wants rows which do have a related row in phpbb_users, an inner joined should be used.

[laserlight]

johanafm wrote:

Except that if he only wants rows which do have a related row in phpbb_users, an inner joined should be used.

I agree, though it is plain that that is not what he wants.

[tomhath]

deleted post