Select Tbl A Recs Where Tbl B Recs Are Missing

[jhaganjr]

Simplified version of the challenge ...

Clients table has a single field - ClientID

Policies table has 3 fields - PolicyID, ClientID, ProductID

Clients has a one to many relationship with Policies based on ClientID, obviously.

I want a list of ClientIDs from Clients where a record does not exist in Policies for those ClientIDs where ProductID = "ABC". In other words ... show me all the clients who do not have ProductID = ABC; i.e. there is no record in Policies for that ClientID and ProductID = ABC.

I've tried many things. Read through many threads. But can't get this to work.

Thanks!

 

[ThomasLafferty]

Maybe I misunderstand your table structure, but if a value of ABC exists in the policy table, and assuming that your ClientID in the Client table is an autonumber key, then even if you don't enter a ClientID, one exists. What this means, then, is that there can not be a record in Policies of ABC without an associated ClientID. Try tweaking your query:

ProductID[!]<>[/!]"ABC"

I may have misunderstodd, but I hope this helps.

Tom

Born once die twice; born twice die once.

 

[lespaul]

SELECT * FROM Client WHERE ClientID NOT IN (SELECT ClientID FROM Policies WHERE ProductID = "ABC")

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jhaganjr]

Thanks Leslie! That's it.

Can you recommend a good reference/training source for SQL? I need to move beyond the Access query designer.

Thanks again.

 

[lespaul]

just start writing your own queries in the SQL view instead of the design view. Read the Join document linked below to get an understanding of how joins work and when to use what kind.

Other than that, it's just practicing to get the data you want and the worse the table structure, the more SQL you have to use to get it to show what you want!

Mine has improved tremendously just by helping out here and seeing what others do. I've learned alot just lurking about!



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[traingamer]

The "Find Unmatched Query Wizard" would have walked you through creation of a query as well.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Another way:
SELECT C.*
FROM Client AS C LEFT JOIN (
SELECT ClientID FROM Policies WHERE ProductID = 'ABC'
) AS P ON C.ClientID = P.ClientID
WHERE P.ClientID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jhaganjr]

Thanks PH! Here's some interesting info (I think) for all to learn from ...

I applied both solutions to my live situation to see if there is a noticable difference in execution time. I timed it from when I clicked the Run button in the query designer to when the selected records displayed in full.

The solution Leslie provided took 43 seconds to complete the display of the queried records. The solution PHV provided took only 2 seconds! I repeated the test several times because I couldn't believe the difference.

Exact same results in a fraction of the execution time. I don't know enough to know why, but then I don't need to know why.

Fascinating.

Thanks to both of you for the great learning opportunity.

Joe

 

[lespaul]

it's because my solution involves a subquery and PHV's uses a left join.....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jhaganjr]

Yeah, that makes sense.

Another interesting thing I've noticed as I'm re-testing and applying the solution in my live data is that the sub-query solution is inconsistent with the quantity of records it delivers. It varies from one to a few records difference each run.

The LEFT JOIN solution has delivered the same quantity of records every time.

Another good lesson learned. Thanks again!