Access / SQL 3 table OUTER JOIN

[cj92713696]

I'm trying to write an ANSI SQL statement to pull data from three tables in both Access & SQL Server. I'd like to create one ANSI SQL statement and use it both in Access and SQL Server 2000.

My three tables are Inventory, GLACNT, and APVEND. I want all data from my Inventory table w/outer joins on GLACNT and APVEND. The data in those two tables may or may not be present. I've illustrated a simple example below w/o any luck. Do I have the syntax wrong for OUTER JOINs? Is there another way to do the below? Also, this may not even ANSI SQL. Any help is appreciated!

SELECT Inventory.ItemCode, GLACNT.Desc, APVEND.Desc
FROM Inventory

LEFT OUTER JOIN GLACNT ON Inventory.StoreCode = GLACNT.StoreCode AND Inventory.AcctNo = GLACNT.AcctNo

LEFT OUTER JOIN APVEND ON Inventory.StoreCode =
APVEND.StoreCode AND Inventory.VendorCode = APVEND.VendorCode

WHERE Inventory.StoreCode = "ABC";

Thanks,
CJ

 

[tlbroadbent]

Neither Access nor SQL Server is totally ANSI compliant. However, the query example you provided should work in Access and SQL Server provided Quoted_Identifier is OFF in SQL Server 7 and higher. Otherwise, SQL uses single quote rather than double quote delimiters. Access will also accept single quote delimiters for literals.

Did you get errors trying to run the query in one or both databases? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cj92713696]

SELECT InventoryItemAssoc.ItemCode, GLACNT.GLAcctNo, GLACNT.GLDesc, APVEND.VendorCode, APVEND.Company

FROM InventoryItemAssoc

LEFT OUTER JOIN GLACNT ON (InventoryItemAssoc.GLAcctNo = GLACNT.GLAcctNo) AND (InventoryItemAssoc.StoreCode = GLACNT.StoreCode)

LEFT OUTER JOIN APVEND ON (InventoryItemAssoc.VendorCode = APVEND.VendorCode) AND (InventoryItemAssoc.StoreCode = APVEND.StoreCode)

WHERE InventoryItemAssoc.StoreCode = "CAR";

This is my Access SQL exactly. I get an error message when I run this in Access. I havn't tried this query in SQL Server yet, but I'm really confused as to why this doesn't work in Access 2k.

Perhaps a syntax issue?

Thanks,
CJ

 

[cj92713696]

One other comment, if I run the above query w/o joining the second table and simply removing the LEFT OUTER JOIN statment on APVEND, the query works fine.

Any suggestions?

Thanks,
CJ

 

[tlbroadbent]

What is the error message? Are the column names correct? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[cj92713696]

http://mail.fat-tuesday.com/error.jpg

lists the exact message.

 

[cj92713696]

I just tried the above SQL syntax on a SQL Server 2k machine w/same exact database schema and it worked perfect. It's definately an issue w/Access.

Is there syntax I can use to do the above in both Access & SQL Server?

Thanks,
CJ

 

[tlbroadbent]

The OUTER keyword is optional in SQL 2000 and Access 2000. I don't have Access 97 installed so I can't test. Perhaps you should remove OUTER form the query and test it. Access 2000 removes "OUTER" when a query is saved and closed.

The parentheses around the JOIN criteria are not needed. Maybe Access 97 doesn't like the parens.

SELECT InventoryItemAssoc.ItemCode, GLACNT.GLAcctNo, GLACNT.GLDesc, APVEND.VendorCode, APVEND.Company
FROM InventoryItemAssoc
LEFT JOIN GLACNT
ON InventoryItemAssoc.GLAcctNo = GLACNT.GLAcctNo
AND InventoryItemAssoc.StoreCode = GLACNT.StoreCode
LEFT JOIN APVEND
ON InventoryItemAssoc.VendorCode = APVEND.VendorCode
AND InventoryItemAssoc.StoreCode = APVEND.StoreCode
WHERE InventoryItemAssoc.StoreCode = "CAR";

Sometimes Access requires extra parens. The following may also be acceptable in Access and SQL Server.

SELECT InventoryItemAssoc.ItemCode, GLACNT.GLAcctNo, GLACNT.GLDesc, APVEND.VendorCode, APVEND.Company
FROM ((InventoryItemAssoc
LEFT JOIN GLACNT
ON InventoryItemAssoc.GLAcctNo = GLACNT.GLAcctNo
AND InventoryItemAssoc.StoreCode = GLACNT.StoreCode)
LEFT JOIN APVEND
ON InventoryItemAssoc.VendorCode = APVEND.VendorCode
AND InventoryItemAssoc.StoreCode = APVEND.StoreCode)
WHERE InventoryItemAssoc.StoreCode = "CAR"; Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.