Solved

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

Posted on 2014-02-03
7
346 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

825 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