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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jeffrey CoachmanMIS LiasonCommented:
Investigate the "Unmatched Query Wizard"
Create-->Other-->Query Wizard-->Unmatched Query Wizard
0
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.