multiple inner join

[murray83]

good evening

have a question which i hipe you clever people can help with

facts first, I have 3 tables which i wish to pull data from

i am able to inner join table 2 to table 1 but then i need data from table 3

i am pulling this using a pass through query and have searched high and low and have tried this

https://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access

and have tried the steps but either says FROM is in wrong place or column ambiguously defined

here is my code help much appreciated

SELECT DISTINCT 
inv.Sku_Id, 
inv.Description AS Short_Description, 
inv.Supplier_Id, 
sk.User_Def_Note_1 AS Long_Description, 
ss.Supplier_Sku_Id

FROM 

((Inventory inv
INNER JOIN SKU sk 
ON inv.SKU_ID = sk.SKU_ID)
INNER JOIN Supplier_Sku ss
ON inv.SKU_ID = ss.SKU_ID)
WHERE  Supplier_Id IS NOT NULL

the following code works fine but i need the Supplier_Sku_Id as well from the table Suplier_Sku

SELECT DISTINCT 

inv.Sku_Id, 
inv.Description AS Short_Description, 
inv.Supplier_Id, 
sk.User_Def_Note_1 AS Long_Description

FROM 

Inventory inv
INNER JOIN SKU sk
on inv.SKU_ID = sk.SKU_ID

WHERE Supplier_Id IS NOT NULL

ORDER BY Supplier_Id ASC

 

[plog]

An ambigously defined column is where you use a reference that isn't specific enough. Like if every table had an ID field and you simply put "ID" in your query without explicitly telling it which table to use. The computer doesn't just guess or randomly pick one of those ID fields, it gives you an error.

The only candidate for that in your query is Supplier_Id in the WHERE and ORDER BY. Every other field you fully reference with a table prefix (inv.SKU_ID, sk.SKU_ID). You need to do that for the Supplier_Id in the WHERE and ORDER BY.

 

[isladogs]

When you alias table names you need to use AS in exactly the same way as you have done for fields e.g Inventory AS Inv

 

[murray83]

thanks a million million plog & isladogs

worked ( of course it did ) and v happy

cant belive that simple but when you look at it yeah how was it supposed to know which supplier id

cheers again guys

 

[SQL_Hell]

When you alias table names you need to use AS in exactly the same way as you have done for fields e.g Inventory AS Inv

Using AS for table name aliases isn't necessary in SQL server.

Either of these two queries will work.

select * from sys.objects so inner join sys.columns sc on sc.object_id = so.object_id

select * from sys.objects AS so inner join sys.columns AS sc on sc.object_id = so.object_id

 

[Gasman]

Nor is it in Access

select * from emails em where em.id = 22

 

[isladogs]

Another myth busted ... PARTLY !

Having tested it, I agree that Access will accept the notation in the last query without using AS.
I've tested it successfully on a couple of fairly complex SQL statements in the VBE.
However if you do this in the query editor, save and reopen the query, Access will always then add the AS if you left it out or restore it if you removed it.

In any case, although it works omitting AS in the VBE, IMHO the statement is significantly clearer if you do use it.