Help w/ query to select all from A where key is NOT in B 2

[southbeach]

I know how to write a join query to select rows from two tables w/ matching key.

I now need to write a query that will list all from table A where a.key is NOT FOUND in table B.

What say you? How can I write this query?

Thank you all in advance for your assistance.

 

[Kozusnik]

Maybe...

SELECT * FROM tableA
LEFT OUTER JOIN tableB on tableA.key=tableB.key
WHERE tableB.key IS NULL


Mark

 

[r937]

slightly better:

SELECT [red]tableA.*[/red] FROM tableA
LEFT OUTER JOIN tableB on tableA.key=tableB.key
WHERE tableB.key IS NULL

another option:

SELECT * FROM tableA
WHERE key NOT IN
( SELECT key FROM tableB )

another:

SELECT * FROM tableA
WHERE NOT EXISTS
( SELECT key FROM tableB WHERE key = tableA.key )


r937.com | rudy.ca

 

[southbeach]

Thank you both for you brilliant posts ... WOW is what came to mind when I read them ...

This is the query I ended up using since my end-user kept on asking for more

if ($_POST['finalized']) $AND = ' AND `wrStatus` != "Q"';
if ($_POST['openonly']) $AND .= ' AND EXISTS (SELECT * FROM productsd WHERE pdQtyOnh > 0 AND pdSysID = quickwr.wrSysID) ';
$sql = 'SELECT * FROM quickwr WHERE wrAccountOfID = ' . $_POST['customer'] . $AND . ' AND `wrBranch` = "' . $_SESSION['user_branch'] . '"';

In the end, we decided to set an couple of check boxes and let the user choose to include/exclude records by checking/unchecking these fields.

I am sure this is not the end of this query but I feel you guys have given me what I need to build upon.

Thank you both again!