Left Joins in Microsoft Query

[FunkyDexter]

I just started a thread in the db forum on whether it's possible to set left joins in excel's 'import external data' query tool and then it occurred to me that the office dev forum might be a better place.

I'd really apreciate it if anyone who knows much about this tool could have a look and post in the other thread (just to prevent duplicate information).

Thanks

[DKenny]

Funky
My preferred approach is to write the Query in the source database and then just reference the resultant query object in MSQuery.


If that is not an option then another way to solve this is to edit the SQL statement generated by MSQuery. Click on -->View-->SQL to show the SQL.
You can then change the SQL to use whatever join path you require, I usually do this in SQLServer Query Analyzer. Once your SQL is correct, paste it back into MSQuery. You will get a message stating that
"SQL Query can't be represented graphically. Continue Anyway?"
Click OK and it will load you SQL statement.
Note: Any future changes to the Query will have to be made in the 'raw' SQL statement.

Here's a before and after version of a query as an example. I was able to use OUTER JOIN in the after and MSQuery accepted the statement.

--Original MS Query SQL
SELECT PH1_TYPE.Type_Desc
, PH2_GROUP.Group_Desc
, PH3_PRODUCT_LINE_PARENT.Product_Desc
FROM PH1_TYPE PH1_TYPE
, PH2_GROUP PH2_GROUP
, PH3_PRODUCT_LINE_PARENT
WHERE PH1_TYPE.Type_ID = PH2_GROUP.Type_ID 
AND PH2_GROUP.Group_ID = PH3_PRODUCT_LINE_PARENT.Group_ID

--Revised SQL
SELECT 	T.Type_Desc
	,G.Group_Desc
	,P.Product_Desc
FROM 	PH3_PRODUCT_LINE_PARENT P
LEFT OUTER JOIN
	(
		PH2_GROUP G
	LEFT OUTER JOIN
		PH1_TYPE T
	ON	G.Type_ID = T.Type_ID 
	)
ON	P.Group_ID =G.Group_ID