Solved

How do I EXCLUDE records from a UNION ALL query?

Posted on 2007-11-19
22
308 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now