[RESOLVED] consolidating query to check for presence in one table and absence in another?

[schwim]

Hi there everyone,

I've performed the necessary check with two queries but wanted to know for the purposes of learning if it was possible to condense this down into one query.

I'm looping through a list of forum users to populate a select box, but have to make sure that their user_id isn't listed in the banlist before printing it. Here's what I'm currently using:

echo("
<h3>Toon: ".$tname." is owned by 
<select name='toon_owner'>
<option value='0'>SELECT ONE</option>
");
//Get the forum users to populate the select box.
$query2 = "SELECT user_id,username FROM phpbb_users WHERE user_type = '0' OR user_type = '3' ORDER BY username ASC";
$result2 = mysql_query ($query2) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query2);
$result_rows2 = mysql_num_rows($result2);
while ($row2 = mysql_fetch_assoc ($result2)) {
	$fid = $row2['user_id'];
	$fname = $row2['username'];
	//Make sure they're not banned before listing them.
	$query3 = "SELECT ban_userid FROM phpbb_banlist WHERE ban_userid = '$fid'";
	$result3 = mysql_query ($query3) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query3);
	$result_rows3 = mysql_num_rows($result3);
	if($result_rows3==0){
		echo("<option value='".$fid."|".$fname."'");
		if($tfid == $fid){
			echo(" selected='selected'");
		}
	}
	echo(">".$fname."</option>\r\n");
}

Is there a way to check for the presence of the id in one table and the absence of the same id in another table all in one query or is this an efficient enough method of handling the situation?

Thanks for your time!

[bradgrafelman]

schwim;11007351 wrote:

Is there a way to check for the presence of the id in one table and the absence of the same id in another table all in one query

Yes; you could use a join or a subquery. I'd probably use the latter, so that's the example I'll give:

SELECT
    user_id, col1, col2
FROM
    table1
WHERE
    user_id NOT IN (
        SELECT user_id FROM table2
    )

In other words, the subquery collects all of the user_id values from table2, and the outer query will then only SELECT the rows form table1 having a user_id value that doesn't appear in table2.

EDIT: Just for completeness (and since I'm on my lunch break), I'll throw in a JOIN example as well:

SELECT
    t1.user_id, t1.col1, t1.col2, t2.user_id
FROM
    table1 t1
        LEFT JOIN t2 ON t1.user_id = t2.user_id
WHERE
    t2.user_id IS NULL

The end result is the same, but you're going about it a bit differently; the LEFT JOIN ensures that all records in table1 get SELECT'ed, meaning the t2.user_id will be set to the NULL value if the JOIN condition can't be satisfied (e.g. there is no matching row in table2). You then utilize that fact in the WHERE clause by eliminating the rows from the combined result set that did satisfy the JOIN condition (e.g. there was a matching row in table2), leaving you with only the rows in table1 that couldn't be JOIN'ed with any from table2.

schwim;11007351 wrote:

is this an efficient enough method of handling the situation?

Definitely not. Any time you see a SQL query being executed in a loop (especially inside of a loop that's processing the results of another SQL query), giant red flags should pop up as it's likely very inefficient. SQL is perfectly capable of handling related sets of data (hint: it's one of the primary reasons to be using it in the first place), but by manually doing the operations/comparisons yourself inside the application, you're tying the DBMS' hands and forcing it to be no smarter (and equally or less efficient) than a flat file parser.

[schwim]

Hi there Brad and thanks for taking the time to help me.

I'm trying to figure out the subquery first before I try to tackle the join. I'm having a problem getting it to work however. Here's my effort:

$query2 = "SELECT user_id,username FROM phpbb_users WHERE user_id AS ban_userid NOT IN ( SELECT ban_userid FROM phpbb_banlist ) ORDER BY UPPER(username) ASC";
$result2 = mysql_query ($query2) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query2);
$result_rows2 = mysql_num_rows($result2);
while ($row2 = mysql_fetch_assoc ($result2)) {
	$fid = $row2['user_id'];
	$fname = $row2['username'];
	echo("<option value='".$fid."|".$fname."'");
	if($tfid == $fid){
		echo(" selected='selected'");
	}
	echo(">".$fname."</option>\r\n");
	echo("
	</select></b>&nbsp;&nbsp;&nbsp;
	<input type='Submit' name='Submit' value='Assign New Owner' />
	</form>

");
}

Which is leaving me with no rows. I don't think I've named anything incorrectly so I'm assuming that the problem lies in the construction of my query. Can you point out where I might have messed up(probably in more than one spot)?

EDIT: Since the placement of the AS part of my query was a complete guess on my part, I also tried:

	$query2 = "SELECT user_id,username FROM phpbb_users WHERE user_id NOT IN ( SELECT user_id AS ban_userid FROM phpbb_banlist ) ORDER BY UPPER(username) ASC";

But that didn't work either.

[jeepin81]

You're close... remove the alias in your sql statement:

$query2 = "SELECT user_id,username FROM phpbb_users WHERE user_id NOT IN ( SELECT ban_userid FROM phpbb_banlist ) ORDER BY UPPER(username) ASC"; 

[jeepin81]

Are you getting an error when you tried what I posted earlier... I didn't notice what you had edited.

[schwim]

Hi there jeepin, thanks very much for your help!

Removing the alias worked perfectly. In my edit, I had tried moving the alias from outside the subquery to inside the subquery, both of which failed. Removing it entirely got it working as it should.

I'm currently trying to build the JOIN example but am having problems figuring out how to move everything over. I think it's all the periods that are confusing me 🙂

[jeepin81]

I think brad was just showing how it can be done by referencing two different tables... you don't need the periods.

$query2 = "SELECT user_id, username FROM phpbb_users
			LEFT JOIN phpbb_banlist ON user_id = ban_userid
			 WHERE ban_userid IS NULL";

[bradgrafelman]

Aye, the table aliases were just added to clarify which table the columns in my examples were referring to (e.g. to remove ambiguity). Since you don't have any duplicated column names in your actual tables, the "table_name.column_name" is superfluous and you can just reference the (unambiguous) column names directly.

[schwim]

Hi there guys,

I'm sorry to bring up a resolved thread, but I attempted to modify the query slightly and have caused an error. I really don't quite understand the usage yet and am wondering if you could help again.

In a nutshell, the difference is that while I'm still looking for the presence of user_id in phpbb_users and the absence of the same variable, but listed as ban_userid in phpbb_banlist, I've tried to add that it should only return where user_id = $fid. This seems to have caused quite a hiccup 🙂

SELECT user_id,username FROM phpbb_users WHERE NOT IN ( SELECT ban_userid FROM phpbb_banlist ) AND user_id = '$fid' AND active = '1'

Could someone set me straight on the way I should have handled this?

[bradgrafelman]

First problem I see is that you have 'WHERE NOT IN' . Where what is NOT IN .. ?

[Derokorian]

Joins are usually better than sub-queries. Also, is there really a line break between the dollar sign and the variable name?

[schwim]

:rolleyes: I should have probably seen that 🙂 Thank you Brad.

[schwim]

No break in between and why are joins better, if you don't mind me asking?

[laserlight]

schwim wrote:

why are joins better

They tend to be more efficient than subqueries. Besides, the schema of a relational database is usually designed with joining related tables in mind.