Solved

How do I EXCLUDE records from a UNION ALL query?

Posted on 2007-11-19
22
324 Views
Last Modified: 2011-03-23
I have written a SQL query in ACCESS that compares the differences in two tables and then returns all the rows of data that do not match between the tables.

However I need to exclude records from TestA that are not in TestB table. If anyone has something that will help I would appreciate it.
SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;

Open in new window

0
Comment
Question by:rcappell
  • 8
  • 7
  • 5
  • +2
22 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20317150
Try changing the first select to a join on the matching column(s)

SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA INNER JOIN TestB ON TestA.TheMatchingColumn = TestB.TheMatchingColumn
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20317201
Most likely your best option is to do an INNER JOIN query between the tables with a NULL check:

SELECT
      TestA.COL1,
      TestA.COL2,
      TestA.COL3,
      TestA.COL4
FROM
      TestA A INNER JOIN TestB B ON B.COL1=A.COL1 AND B.COL2=A.COL2 AND B.COL3=A.COL3 AND B.COL4=A.COL4

0
 

Author Comment

by:rcappell
ID: 20317268
I think I need to explain myself a little better AGX your solution worked but only to a point.  I need all the information from both tables that has a matching issue. Not just the records from TestA that don't match the same records sorce in TestB.  That's why I used the UNION ALL.  What I don't need are any records that are in TestA but not in Test B.

Both tables have the same colum headers and data types.   Does this help?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 25

Expert Comment

by:imitchie
ID: 20317271
how do you identify
"records from TestA that are not in TestB table"?  by which combination of columns
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20317290
rcappell,

My question would be the same as imitchie's:  how do you identify  "records from TestA that are not in TestB table"?  

0
 

Author Comment

by:rcappell
ID: 20317300
To get all the records that are mismatched my union looks for differences in each column.  And I think that is part of my problem, but every time I tell it to join on a specific field it (INNER JOIN) it defaults to only on table.

Did that make sense??? I have been working this silly thing for two days and I am starting to go around in circles and re-writing logic that has already failed.
0
 

Author Comment

by:rcappell
ID: 20317311
I have written a second SQL query that gives me the list of records in TestA but are not in TestB. But I can't figure out how to use it with the other query.  Unfortunately it's not in this copy of the database so it would take me a bit to recreate it.  
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317315
aren't all differences in TestB automatically "not in TestA" by definition?

TestA
1  2  3  4
2  4  6  7
3  5  7  8
TestB
1  2  3  5
3  4  8  9

which ones are "different" (why) and which ones are "not in A"??
As far as I can see, all of the ones in TestB are not in A
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20317326
>  I need all the information from both tables that has a matching issue

I think I understand but I think you'd need a shared key to do that.  Its not clear what keys are or what constitutes matching issue.  I understand you've obscured confidential information (Col1, Col2, Col3, Col4) but what is the relationship between the two tables?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317330
it would then simply boil down to

SELECT TestB.COL1, TestB.COL2, TestB.COL3, TestB.COL4
FROM TestB
   LEFT JOIN TestA on TestA.Col1 = TestB.Col1
     AND TestA.Col2 = TestB.Col2
     AND TestA.Col3 = TestB.Col3
     AND TestA.Col4 = TestB.Col4
WHERE Coalesce(TestA.Col1, TestA.Col2, TestA.Col3, TestA.Col4) is null
0
 

Author Comment

by:rcappell
ID: 20317359
TestA
Col1  Col2  Col3
1        av     ab
1        ga     gg
2        jo      oo
2        io      ao
3        aa    bb
TestB
Col1  Col2  Col3
1       av      ab
1       ag      hh
3       aa      bb
Result
Col1   Col2   Col3
1        ga     gg    (from TestA)
1       ag      hh    (from TestB)

You will notice that not all rows of 1 moved over because they weren't all different. Also you will notice that none of the 2's came over that is because they are in Test A only .  Records identified by the 3 were matches.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317379
So Col1 is a key link between TestA and TestB ?

TestA
Col1  Col2  Col3
1        ga     gg
TestB
Col1  Col2  Col3
1       av      ab
1       ag      hh
Result
Col1   Col2   Col3
??
0
 

Author Comment

by:rcappell
ID: 20317403
I am not sure what you are displaying in you example but for arguments sake we can make Col1 the key.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 125 total points
ID: 20317445
SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA WHERE COL1 IN (SELECT COL1 FROM TestB)  --++
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;
0
 

Author Comment

by:rcappell
ID: 20317463
imitchie I will give your logic a try but can you explain what the symbols mean at the end of the following.
  FROM TestA WHERE COL1 IN (SELECT COL1 FROM TestB)  --++

I hate to ask and appear less than intelligent but I am a beginner at this and I fear it is showing.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317476
rcappell, sorry for confusing you.  SQL Server comments can be put in after --.
I was trying to draw attention to where I have made the change.
you can put comments anywhere by putting -- and then the comment behind it

i.e

select *
from mytable
where id < 4  -- 4 being the filter point

but it may not even work with the Access query designer. try this instead
SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA WHERE COL1 IN (SELECT COL1 FROM TestB)
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 20317495
It seems like you're back to the first query then (substituting Col1 for "TheMatchingColumn" of course).  But I thought rcappell said that didn't work :/ ..



SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA INNER JOIN TestB ON TestA.Col1 = TestB.Col1
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;

Open in new window

0
 

Author Comment

by:rcappell
ID: 20317531
Well agx and imitchie the solution worked.  I am not sure what the difference is except in imitchie's answer he is using a WHERE and the other is an inner join.

Using imitchie's code it returns exactly what I needed.

Thank you all very much.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317546
for reference:  _Agx_  and others viewing this solution,

SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA INNER JOIN TestB ON TestA.Col1 = TestB.Col1

causes data from TestA to be replicated many times for EACH record in TestB of the same Col1.
0
 

Author Comment

by:rcappell
ID: 20317568
Thanks so much imitchie that was one of the major issues I was running into trying to do this using the query builders in Access and why I opted for SQL statements.  I will click the accept on your solution.  Again I greatly appreciate all the help and will be back for more.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20317616
@imitchie - thanks for the explanation.  I thought Col1 was supposed to be a unique key, but obviously its not ;-)

0
 

Expert Comment

by:Glenn_g
ID: 35200592
@imitchie, many thanks just needed the same code but excluding the duplicates.

I used the query so I could generate a query from mutliple data sources (table A)
where manual changes are done in a second table (table B)

Where the query hereunder takes the values from A where no updates are found in table B

again MANY thanks!

SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
   FROM TestA WHERE COL1 NOT IN (SELECT COL1 FROM TestB) 
   UNION ALL
   SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
  FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3; 

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question