Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-03
7
Medium Priority
?
386 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

885 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