Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

How do I EXCLUDE records from a UNION ALL query?

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
rcappell
Asked:
rcappell
  • 8
  • 7
  • 5
  • +2
1 Solution
 
_agx_Commented:
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
 
digital_thoughtsCommented:
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
 
rcappellAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imitchieCommented:
how do you identify
"records from TestA that are not in TestB table"?  by which combination of columns
0
 
_agx_Commented:
rcappell,

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

0
 
rcappellAuthor Commented:
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
 
rcappellAuthor Commented:
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
 
imitchieCommented:
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
 
_agx_Commented:
>  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
 
imitchieCommented:
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
 
rcappellAuthor Commented:
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
 
imitchieCommented:
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
 
rcappellAuthor Commented:
I am not sure what you are displaying in you example but for arguments sake we can make Col1 the key.
0
 
imitchieCommented:
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
 
rcappellAuthor Commented:
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
 
imitchieCommented:
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
 
_agx_Commented:
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
 
rcappellAuthor Commented:
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
 
imitchieCommented:
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
 
rcappellAuthor Commented:
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
 
_agx_Commented:
@imitchie - thanks for the explanation.  I thought Col1 was supposed to be a unique key, but obviously its not ;-)

0
 
Glenn_gCommented:
@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 On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 7
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now