Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I EXCLUDE records from a UNION ALL query?

Posted on 2007-11-19
22
Medium Priority
?
356 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

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…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview
Suggested Courses

971 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