MS excel Query with 3+ tables and 2+ outer Joins

[konkolzak]

Hi Everyone,
I'm trying to use MS query via ODBC connection to pull data from one main table while joining two tables (left joins each) to enrich the data. I know that MS query wizard only supports one outer join, but from what I've read, the correct SQL code will work.

Think of it like this:
Table A has customer level detail
Table B has territory groupings
Table C has product mapping


Here's the base on what I'm trying to do:
Two Table with one left join that works looks like this-

Select TableA.Field1, TableB.Field2
From {oj TableA LEFT OUTER JOIN TableB ON TableA.Field4=TableB.Field4}
Where (TableA.Field1='x')



I would think that adding in a second left join would look like something like this:
Select TableA.Field1, TableB.Field2, TableC.Field3
From {oj TableA LEFT OUTER JOIN TableB ON TableA.Field4=TableB.Field4 AND (oj TableA LEFT OUTER JOIN TableC On TableA.Field5=TableC.Field5)}
Where (TableA.Field1='x')

Any ideas how a person would add in a second (or third, fourth, etc) Left Join/Outer Join? Since other applications (e.g., Hyperion Brio) allow for multiple outer joins, I feel like MS has to....and it's just that the wizard doesn't visually support multiple outer joins.

Thanks for the help

 

[jduley]

Is using MS Access an option for you?

 

[Jerry Sullivan]

Any ideas how a person would add in a second (or third, fourth, etc) Left Join/Outer Join? Since other applications (e.g., Hyperion Brio) allow for multiple outer joins, I feel like MS has to....and it's just that the wizard doesn't visually support multiple outer joins.

You're correct that MS Query will allow for multiple joins, it just can't display the query graphically.

Here's one way to accomplish what you describe using a sub query....

SELECT T1.Field1, T1.Field2, c.Field3
FROM
  (SELECT a.Field1, b.Field2, a.Field5  
   FROM  TableA a
   LEFT JOIN TableB b  
   ON a.Field4 = b.Field4
   WHERE a.Field1='x') AS T1
 LEFT JOIN TableC c
 ON T1.Field5 = c.Field5

I'm still a novice at SQL so there might be better ways to do this; however this shows it's possible using MS Query.

 

[konkolzak]

Hi Jerry,
Thanks for the note. I'm new to SQL as well so this is all Greek to me.

The below doesn't seem to be working in MS Query. It's bugging out on the last line. For some reason it's not liking the last line. I keep getting
"c.Field5" is not valid in the contxt where it is used. SQLSTATE = 42703

Any idea why/what needs to be updated? Thanks for the help!

You're correct that MS Query will allow for multiple joins, it just can't display the query graphically.

Here's one way to accomplish what you describe using a sub query....

SELECT T1.Field1, T1.Field2, c.Field3
FROM
  (SELECT a.Field1, b.Field2, a.Field5  
   FROM  TableA a
   LEFT JOIN TableB b  
   ON a.Field4 = b.Field4
   WHERE a.Field1='x') AS T1
 LEFT JOIN TableC c
 ON T1.Field5 = c.Field5

I'm still a novice at SQL so there might be better ways to do this; however this shows it's possible using MS Query.

 

[Jerry Sullivan]

It works for me in a mockup of the 3 tables you describe.

Double-check that there are no mismatches in the names of the Tables and fields between the code and your actual data.

After that, I'd try doing it without using the alias of TableC as c ....

SELECT T1.Field1, T1.Field2, TableC.Field3
FROM
  (SELECT a.Field1, b.Field2, a.Field5  
   FROM  TableA a
   LEFT JOIN TableB b  
   ON a.Field4 = b.Field4
   WHERE a.Field1='x') AS T1
 LEFT JOIN TableC TableC 
 ON T1.Field5 = TableC.Field5