Listbox Query Help

[dchendrickson]

I am running Access2002 / XP Pro.

The query forum is down right now so I posted here.

I need some help jumpstarting my brain. I need to build
an SQL for a listbox. The query needs to find records NOT
found in another table. The structure is like this:

tblMain has an MainID field that is an autonumber and
some other fields. tblDetails is a subdatasheet to
tblMain and is linked through the MainID. In tblDetails,
MainID and OtherID form the Primary Key. Not every entry
in tblMain will have an entry in tblDetails.

I need an SQL statement that will find all the records in
tblMain that do NOT have an entry in tblDetails.

I know how to get all the records that DO have
corresponding entries in tblDetails:

SELECT m.MainID FROM tblMain AS m INNER JOIN tblDetails
AS d ON m.MainID = d.MainID;

Where does the 'NOT' fit into this?

Thanks (it has to be a Monday)!

-dc

 

[GreySky]

I believe it's called a "frustrated outer join."

Select MainID From tblMain
Left Join tblOrders On tblMain.MainID = tblOrders.MainID
Where tblOrders.OrderID Is Null

The essence to understanding it is understanding that
queries first build a Cartesian Product Table.

Example:

tblMain
MainID Name
1 Arcana
2 Joe
3 Mary

tblOrders
OrderID MainID Item
1 1 ItemA
1 1 ItemB
1 2 ItemC

If you add these to your query view, and then choose left
join, the resulting CPT is:

MainID OrderID
1 1
1 1
2 2
3 (Null)

So what the Where statement says is, "give me nulls."
Your answer is MainID #3 (has no orders).

David Atkins, MCP

 

[Graham Mandeno]

Hi DC

Use a "left join" query and filter for null in the related field:

Select M.MainID, M.TextField from tblMain as M
left join tblDetails as D on M.MainID=D.MainID
where D.MainID is Null;

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[John Vinson]

I need an SQL statement that will find all the records in
tblMain that do NOT have an entry in tblDetails.

This requires an exercise in pretzel logic which I call a "Frustrated
Outer Join" query. This is best understood after ones' second cup of
coffee or mental stimulant of choice!

Create a query joining tblMain to tblDetails. This, of course, gives
you all the records where tblDetails DOES have a match.

Now select the Join line and choose Option 2 - "Show all rows in
tblMain and matching records in tblDetails". This now shows all
records in tblMain, whether they match or not (so you're apparently
even further from your goal! but fear not.)

Select ONLY the joining field from tblDetails, and all the fields that
you want to see from tblMain; put a criterion on the tblDetails
joining field of

IS NULL

Since the Outer Join query fills in NULL for non-matched records in
the tblDetails side of the query, this eliminates all the composite
records which *do* have a match, leaving only the unmatched ones.

 

[BK]

I'm not sure if this is any better or worse than the others but you
could also use a subquery to do this.

Something like:

SELECT * FROM tblMain WHERE MainID NOT IN (SELECT MainID FROM tblDetails)

HTH
Bill

 

[dchendrickson]

WOW! Thanks to all - may ways to skin a cat.

While waiting for responses, I dug and found and created
and finally got something that works too. But I have no
idea if it is 'good practice', 'efficient', or all around
garbage. Comments please again.

SELECT MainID, ...other FROM tblMain as M WHERE Exists
(SELECT * FROM tblDetails AS D WHERE d.MainID = m!MainID
=False;


Thanks so much for the great input. I am glad to hear
this is a '2-cups of joe' issue.

-dc

 

[Graham Mandeno]

Hi again

A subquery like this (or like Bill's) will work, but not as efficiently as a
frustrated left join, as suggested by John, David, and myself.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[dchendrickson]

Graham,

Thanks for the info again. I have changed to the Left
join and all is well. And now I know that the 'Exists'
construct I cobbled together isn't the best choice.

I'm learnin'. By the time this project is done you can
call me Expert (I was once told an "ex" is a has-been and
a "spert" is a drip under pressure).

-dc

-----Original Message-----
Hi again

A subquery like this (or like Bill's) will work, but not as efficiently as a
frustrated left join, as suggested by John, David, and myself.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

WOW! Thanks to all - may ways to skin a cat.

While waiting for responses, I dug and found and created
and finally got something that works too. But I have no
idea if it is 'good practice', 'efficient', or all around
garbage. Comments please again.

SELECT MainID, ...other FROM tblMain as M WHERE Exists
(SELECT * FROM tblDetails AS D WHERE d.MainID = m! MainID
=False;


Thanks so much for the great input. I am glad to hear
this is a '2-cups of joe' issue.

-dc

.