Solved

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

Posted on 2014-02-03
7
340 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 12

Assisted Solution

by:duttcom
duttcom earned 100 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 142

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

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

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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 200 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

920 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