Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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

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
upobDaPlaya
Asked:
upobDaPlaya
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
duttcomCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Paul_Harris_FusionCommented:
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
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.

 
upobDaPlayaAuthor Commented:
So can you do an INNER join in a UNION query ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Paul_Harris_FusionCommented:
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
 
upobDaPlayaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now