sql question

hi all,
I have two tables with 3 identical fields (example below), how can I view all records from tableA that don't have an corresponding match in tableB.

TableA                                               TableB
----------                                               ---------------------
ID                                                        ID
EmployeeID                                       EmployeeID
FacilityID                                            FacilityID

Now I need a query that gives me all records from tableA that the combination of EmployeeID+FacilityID do not exists in TableB.
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
SELECT * FROM TableA
WHERE ID NOT IN (SELECT ID FROM TableB)
0
dsackerContract ERP Admin/ConsultantCommented:
That may not work. Access doesn't like the "IN" statement. Try this:

SELECT TableA.*
FROM TableA
WHERE NOT EXISTS
(
    SELECT TableB.ID
    FROM TableB
    WHERE TableB.ID = TableA.ID
)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above answers using IN and EXISTS are correct.  
As an aside, the below image will help your understanding of answering this question via JOINs.
SQL Joins
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bfuchsAuthor Commented:
hi, thanks for replying, the problem is that this is an access db and there (using this not in technique), with just more then few hundred records it takes forever to process...
0
dsackerContract ERP Admin/ConsultantCommented:
I added an EXISTS option.
0
QuinnDexCommented:
in and exists was my first thought, but poster wasnt asking for the id column to be compared he was asking for the other 2 columns to be compared

this lead me to believe that posibly either EmployeeID or FacilityID may be missing from one of the tables

@bfuchs can you confirm what you need please
0
winheimCommented:
try it through a left join  and join all fields which should be same. With the WHERE clause you can exclude all non matches from TableB with TableA

SELECT TableA.*, TableB.ID
FROM TableA LEFT JOIN TableB ON (TableA.FacilityID = TableB.FacilityID) AND (TableA.EmployeeID = TableB.EmployeeID)
WHERE TableB.ID Is Null;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
dsacker: wondering why should the exists be better in this case, as to my understanding the problem lays in access performing this sub query for every record on TableA?

jimhorn: Just want to clarify, what I need is the opposite, not to join them by id, the join must be by the other two fields, I know how to perform this when I need to find all EmployeeID from tableA that are not in TableB, however in this case I need the combination of two fields, for example if record 1 in tableA has EmployeeID 1000 and FacilityID 2000, and in tableB there is no record with those values (Employeeid 1000 + FacilityID 2000) then this should come up in the results.
0
QuinnDexCommented:
does the id column for the 2 tables match, ie would you expect if the record existed that tableB record 1 would be the same as record 1 tableA
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>the join must be by the other two fields,
Same principle, but winheim's answer is correct with the LEFT JOIN ON .. AND ...

btw, what is the data type of EmployeeID and FacilityID?  Numeric of any kind would be good, the smaller the better, and the reason I ask is because if it's a Text of any kind, especially Text(255) any JOIN will be very slow.
0
Rey Obrero (Capricorn1)Commented:
try this query

select A.*
from A
Left join B on A.FacilityID=B.FacilityID and A.EmployeeID=B.EmployeeID
Where B.FacilityID Is Null and B.EmployeeID is Null


if you want to see the reverse


select B.*
from B
Left join A on B.FacilityID=A.FacilityID and B.EmployeeID=A.EmployeeID
Where A.FacilityID Is Null and A.EmployeeID is Null
0
bfuchsAuthor Commented:
winheim: Will try yours first and let you know.

 QuinnDex: the id are irrelevant as each  table has their own counter.

 jimhorn: all datatypes are numbers in access.

capricorn1: your solution seems to be the same as winheims except that you're including both fields in the where instead of the id (Where A.FacilityID Is Null and A.EmployeeID is Null), not sure if that would work as desired..
0
QuinnDexCommented:
the only way i can see to do this is move all that match tableA in tableB to a temptable, as these you have something to match on, then select from table b where id not in temptable
0
Rey Obrero (Capricorn1)Commented:
bfuchs,

did you try the query?

better upload a copy of your db..
0
PortletPaulfreelancerCommented:
>>how can I view all records from tableA that don't have an corresponding match in tableB.
I suggest you try the queries from capricorn1 (using a LEFT JOIN)

there should be no need to move the data into a temp table by the way.
>>the id are irrelevant as each  table has their own counter.
for any queries using TableA.ID = TableB.ID

replace that with

     TableA.EmployeeID = TableB.EmployeeID
and TableA.FacilityID = TableB.FacilityID

e.g.
SELECT TableA.*
FROM TableA
WHERE NOT EXISTS
(
    SELECT TableB.ID
    FROM TableB
    WHERE TableA.EmployeeID = TableB.EmployeeID
    AND TableA.FacilityID = TableB.FacilityID
)
>>using this not in technique), with just more then few hundred records it takes forever to process
take care with any use of IN()
each and every value contained inside those parentheses is treated as an OR condition and so it can be very slow.

|NOT | EXISTS ()
is a much better option than IN() for a large number of possible values

{+edit} NO points pl.
0
bfuchsAuthor Commented:
hi folks, thanks all for your replies and suggestions, they all seem to do the job, however I think its only fair to promote winheim (ID: 39458911) as it was the first solution that worked efficient in my scenario.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.