Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How is it possible to do a INNER JOIN on a UNION ALL query

Posted on 2014-02-03
7
Medium Priority
?
381 Views
Last Modified: 2014-02-12
I am perplexed on the below code as I thought with a UNION ALL query there is no such thing as an INNER JOIN.  I thought the purpose of the UNION is to combine to disparate set of records and eliminate duplicates ?

Can someone educate me on this...

Note this is done within MS Excel using ADO....all of it works when run, but I am trying to better understand the UNION component

strSQLStm1 = .................................
strSQLStm2 = .................................

strSQLStm = "Select A.[Client], B.[Order], B.[Amount] " & _
"FROM " & strSQLStm1 & INNER JOIN " & strSQLStm2 & " ON A.CLIENT = B.CLIENT UNION ALL "
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Assisted Solution

by:duttcom
duttcom earned 400 total points
ID: 39831578
The results of the queries being joined by the union must result in exactly the same columns, eg. from a table of suppliers and customers -

Select Name, Address
from SuppliersTable
where Condition 1
UNION
Select Name, Address
from CustomersTable
where Condition

The results will have name and address but contain records from both tables.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39831740
Firstly, I would recommend to move the complex sql into stored procedure, if possible

Anyhow  the structure would be like

Select
From  table
Join ( select ... union select ... )
    On ...

 Or indeed
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 39831939
We can't see your full SQL statement because of the use of variables.

However,  as far as I can see,  that statement would not execute.

The union all has to have two tables (or subqueries) either side of it.

e.g.  
Select Col1, Col2 from Table1
UNION ALL
Select Col1, Col2 from Table2

It makes no sense for the statement to end with UNION ALL.

Further to your question,  the ALL part explicitly allows duplicates to remain in the result set.  If you want to eliminate duplicates use UNION or UNION DISTINCT
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!

 

Author Comment

by:upobDaPlaya
ID: 39834629
So can you do an INNER join in a UNION query ?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 39834899
yes, you can.
it's just a matter of "formatting" the query according to where you need to apply the JOIN on, either on one of the tables, or on the result of the union.
you just have to put the "brackets" and the syntax as you need it.
0
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 800 total points
ID: 39835141
Your original question said you were perplexed by the code - and rightly so, it made no sense!

When you now ask:   'So can you do an INNER join in a UNION query ? '
There are two ways of interpreting it and the answer is yes in both cases.

1.  Join to a Union query
Select T2.ID, T2.Col1, Q.Val
from Table1 T1
Inner join
( Select ID, Val from Table2
  UNION ALL
  Select ID, Val from Table3
) Q
on T1.ID = Q.ID

Open in new window


2.  Union with a joined query
Select T1.ID, T1.Val
UNION ALL 
(Select T2.ID, T3.Val 
 from Table2 T2 inner join Table3 T3
 on T2.ID = T3.ID
) 

Open in new window

0
 

Author Closing Comment

by:upobDaPlaya
ID: 39855219
Excellent responses.  I took some time to review and I am glad to report it is definitely making sense thanks to your responses above.  thx !
0

Featured Post

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

705 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