Rows in one table not in another

Hi,

I have 2 tables in MS Access which have identical columns

Table A has C1,C2,C3,C4,C5,C6
Table B has C1,C2,C3,C4,C5,C6

in both the tables C2,C3,C4 are primary keys.

How do I find the rows in Table A which are not in Table B?

Is this correct?
-----------------------------------------------------------
select * from TableA a left outer join TableB b

on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4

where b.C1 is null or b.C5 is null or b.C6 is null
sunny82Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mbizupCommented:
Thats close, but in Access use LEFT JOIN:


select * from TableA a LEFT JOIN TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C1 is null or b.C5 is null or b.C6 is null 

Open in new window

0
mbizupCommented:
You might be able to get by just checking the first PK for Null.  It's a required field - no nulls allowed, right?  


select * from TableA a LEFT JOIN TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C1 is null 

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Investigate the "Unmatched Query Wizard"
Create-->Other-->Query Wizard-->Unmatched Query Wizard
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

SharathData EngineerCommented:
try like this.
select * from TableA a left outer join TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C2 is null and b.C3 is null and b.C4 is null

Open in new window


or try this.
select * from TableA as a where not exists (select 1 from TableB as b where a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4)

Open in new window

0
sunny82Author Commented:
I will try the solutions tomorrow. But one thing I would like to correct is that the keys are not primary keys as they may have null values in them. The combination of C2,C3 and C4 will have atleast one value in them in each of the rows.


So I guess it's better to redefine them as unique keys?
0
sunny82Author Commented:
Ok I tried the solutions. I created 2 tables TableA and TableB. I did not create any keys since the columns on which I am joining may contain some nulls, though the combination of C2,C3,C4 even if they contain nulls must be unique. Also TableB is an exact copy of TableA along with the same data, hence I expect the queries to not return any rows since they should give me the rows in one table but not in another table.

The solutions work perfectly when the joining columns C2,C3 and C4 are not nulls. But when they have nulls, the results seem strange.

I am attaching the tables and results here.

Result 1 tab is for Queries with both returning same results ---
----------------------------------------------------------
select * from TableA a left outer join TableB b
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C2 is null and b.C3 is null and b.C4 is null

and

select * from TableA as a where not exists (select 1 from TableB as b where a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4)

-------------------------------------------------------------

Result2 tab is for query ----
-------------------------------------------------------
select * from TableA a LEFT JOIN TableB b
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C1 is null or b.C5 is null or b.C6 is null  
--------------------------------------------------------


What is the problem here?
0
sunny82Author Commented:
Here is the attachment..
Tables-and-Results.xls
0
mbizupCommented:
Try this -


select * from TableA a LEFT JOIN TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where (b.C1 is null AND b.C5 is null AND b.C6 is null) 

Open in new window

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
mbizupCommented:
Or this:

select * from TableA a LEFT JOIN TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where (b.C1 is null AND b.C2 is null AND b.C3 is null AND b.C4 is null AND b.C5 is null AND b.C6 is null) 

Open in new window



In an 'unmatched' query all fields in the table without a match will be null...
0
sunny82Author Commented:
Hi mbizup,

Pls check Result 3 tab for the results. It still returns some rows.
Tables-and-Results.xls
0
sunny82Author Commented:
I am trying the second query now...
0
sunny82Author Commented:
here are results (Result 4 tab)for the second query, its the same as Result 3 tab
Tables-and-Results.xls
0
mbizupCommented:
Hmm.   Can any/all of the joined fields be null?
0
sunny82Author Commented:
Yes some of them can be null...as you can see from my spreadsheet...

Some values in joined fields C2,C3,C4 are null though the combination of C2 + C3 + C4 should be unique in identifying any row..
0
mbizupCommented:
... And that is what is throwing the monkey wrench in the works.

Lets try this:

select * from TableA a LEFT JOIN TableB b 
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C2 & b.C3 & b.C4 & "" <>   a.C2 & a.C3 & a.C4 & ""

Open in new window

0
sunny82Author Commented:
This query works fine since tables are identical and they return no rows.

But the problem is, say in TableA if I change the row 3 col1 value to say 99 (So the 1st column in TableA now has 1,7,99 while 1st column in TableB has 1,7,12 as was earlier), it still returns no rows. Now it should return the value 99 right since it is in TableA but not in TableB?
0
mbizupCommented:
Okay - I thought you were only trying to match up certain columns.

Does this work?


select * from TableA a LEFT JOIN TableB b
on a.C2= b.C2 and
     a.C3 = b.C3 and
     a.C4 = b.C4
where b.C1 & b.C2 & b.C3 & b.C4 & b.C5 & b.C6 "" <>  a.C1 &  a.C2 & a.C3 & a.C4 & a.C5 & a.C6 & ""


0
mbizupCommented:
If that doesn't return the results you are looking for, please post sample data with expected results showing all variations you might have...
0
sunny82Author Commented:
Attached are the results (Result 5 tab) from the above query. I have also modified TableA (in New Table A tab). New Table A tab contains Row 3 Col 1 value as 99. That is the only change from earlier. Table B remains unchanged.

So the difference between the two tables is only in the 3rd row. So only the third row of New Table A should show in the results

But if you see Results 5 tab, The entire third row is coming which is absolutely perfect and expected. But there is an extra 2nd row also coming from New Table A which should not be there. Why is this coming?
Tables-and-Results.xls
0
sunny82Author Commented:
In Sybase, this query is giving me perfect results what I need...

------------------------------------------------------------------------

select * from TableA a

where  exists

    (select * from TableB b

        where  

        isnull(b.c2, 'AA') = isnull(a.c2, 'AA')

        and isnull(b.c3, 'AA') = isnull(a.c3, 'AA')

        and isnull(b.c4, 'AA') = isnull(a.c4, 'AA')

        and (
         isnull(b.c1, 'AA') <> isnull(a.c1, 'AA')

        or isnull(b.c5, 'AA') <> isnull(a.c5, 'AA')

                    or isnull(b.c6, 'AA') <> isnull(a.c6, 'AA')

                )

    )
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

But when I am implementing the same in Access, its giving me the error"undefined function iff" error. Why is this happening? Where am I going wrong with the syntax?

------------------------------------------------------------------------------

select * from TableA a

where exists

  (select * from TableB b
   where  iff(b.C2 = "",'AA',b.C2) = iff(a.C2 = "",'AA',a.C2)
   and      iff(b.C3 = "",'AA',b.C3) = iff(a.C3 = "",'AA',a.C3)
   and      iff(b.C4 = "",'AA',b.C4) = iff(a.C4 = "",'AA',a.C2)
   and     (iff(b.C1 = "",'AA',b.C1) <>  iff(a.C1 = "",'AA',a.C1)
           or iff(b.C5 = "",'AA',b.C5) = iff(a.C5 = "",'AA',a.C5)
           or iff(b.C6 = "",'AA',b.C6) = iff(a.C6 = "",'AA',a.C6)
              )
  )







-----------------------------------------------------------------------------
0
sunny82Author Commented:
How can I do the above Sybase query in Access?
0
sunny82Author Commented:
This worked great, just used nz

select * from TableA a

where exists

  (select * from TableB b
   where  nz(b.C2,"AA") = nz(a.C2,"AA")
   and      nz(b.C3,"AA") = nz(a.C3,"AA")
   and     nz(b.C4,"AA") = nz(a.C4,"AA")
   and  
(  
 nz(b.C1,"AA") <> nz(a.C1,"AA")
           or nz(b.C5,"AA")<> nz(a.C5,"AA")
          or  nz(b.C6,"AA")<> nz(a.C6,"AA")
             )
 )
0
mbizupCommented:
In Access your iffs should be IIf.

Your isnulls should be Nz.

This is assuming you are using access for both your data storage and your user interface.   It won't work from a .Net application for instance.
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.