Join Types

[Guest]

I'm aware of the Inner, Left Outer, and Right Outer joins. I'm looking for
something a little different, if it even exists.

I've got a table (tblA) with Product_ID and Amount. I've got another table
(tblExcl) that contains a list of Product_ID's I want to EXCLUDE from tblA.
I know I can do a Delete query, but for reasons too lengthly to go into here,
I'm looking for an alternative solution.

Is there a join type that would EXCLUDE the Product_ID's in tblExcl from
tblA? I know it's probably a direct SQL command, nothing available in the
QBE.

 

[Rick Brandt]

I'm aware of the Inner, Left Outer, and Right Outer joins. I'm
looking for something a little different, if it even exists.

I've got a table (tblA) with Product_ID and Amount. I've got another
table (tblExcl) that contains a list of Product_ID's I want to
EXCLUDE from tblA. I know I can do a Delete query, but for reasons
too lengthly to go into here, I'm looking for an alternative solution.

Is there a join type that would EXCLUDE the Product_ID's in tblExcl
from tblA? I know it's probably a direct SQL command, nothing
available in the QBE.

Use a Left Outer Join from tblA to tblExcl and use a criteria of...

WHERE tblExcl.FieldName Is Null

That will return all rows from tblA that DO NOT have a match in tblExcl.

 

[Dirk Goldgar]

I'm aware of the Inner, Left Outer, and Right Outer joins. I'm
looking for something a little different, if it even exists.

I've got a table (tblA) with Product_ID and Amount. I've got another
table (tblExcl) that contains a list of Product_ID's I want to
EXCLUDE from tblA. I know I can do a Delete query, but for reasons
too lengthly to go into here, I'm looking for an alternative solution.

Is there a join type that would EXCLUDE the Product_ID's in tblExcl
from tblA? I know it's probably a direct SQL command, nothing
available in the QBE.

This is essentially what the Query Wizards calls a "find unmatched
query". You can build it quite easily in the QBE window, but here's the
basic SQL:

SELECT *
FROM
tblA
LEFT JOIN
tblExcl
ON tblA.ProductID = tblExcl.ProductID
WHERE tblExcl.ProductID Is Null;

Note: that is a query to return all the records in tblA that are not
matched in tblExcl. If you wanted to actually *delete* the matched
records, the SQL would be different.

 

[Guest]

Perfect!

Thanks for the help!

Use a Left Outer Join from tblA to tblExcl and use a criteria of...

WHERE tblExcl.FieldName Is Null

That will return all rows from tblA that DO NOT have a match in tblExcl.

 

[Guest]

Perfect!

Thanks for the help!

This is essentially what the Query Wizards calls a "find unmatched
query". You can build it quite easily in the QBE window, but here's the
basic SQL:

SELECT *
FROM
tblA
LEFT JOIN
tblExcl
ON tblA.ProductID = tblExcl.ProductID
WHERE tblExcl.ProductID Is Null;

Note: that is a query to return all the records in tblA that are not
matched in tblExcl. If you wanted to actually *delete* the matched
records, the SQL would be different.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)