multiple inner join (1 Viewer)

murray83

Games Collector
Local time
Today, 09:38
Joined
Mar 31, 2017
Messages
728
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

Code:
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

Code:
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

Banishment Pending
Local time
Today, 04:38
Joined
May 11, 2011
Messages
11,597
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

MVP / VIP
Local time
Today, 09:38
Joined
Jan 14, 2017
Messages
18,164
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

Games Collector
Local time
Today, 09:38
Joined
Mar 31, 2017
Messages
728
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

SQL Server DBA
Local time
Today, 09:38
Joined
Dec 4, 2003
Messages
1,360
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

Enthusiastic Amateur
Local time
Today, 09:38
Joined
Sep 21, 2011
Messages
13,964
Nor is it in Access
Code:
select * from emails em where em.id = 22
 

isladogs

MVP / VIP
Local time
Today, 09:38
Joined
Jan 14, 2017
Messages
18,164
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.
 

Users who are viewing this thread

Top Bottom