CODEWITHSUNDEEP
sqlinner-joinalias
Ibrahiem Rafiq
Ibrahiem Rafiq

Reputation: 73

Joins Using Multiple Tables

Really struggling here and don't quite understand whats happening. Using MS SQL Server 2012.

IF I was to to run this code:

USE database 1;
GO

SELECT a.ID, b.ID, c.ID, d.ID
FROM table1 a, table2 b, table3 c, table3 d
INNER JOIN database2.tablex tx ON a.ID = tx.ID

It won't work for some reason. Intellisense will only display the last alias in the list so "d". If I write the rest of the code out it syays xxxx column could not be bound... How can I do this? I want to select from multiple tables and then apply inner joins for all them tables (building a DW).

Thanks

Upvotes: 0

Views: 404

Answers (1)

a1ex07
a1ex07

Reputation: 37354

Don't mix 2 styles of joins - the old one (join condition in WHERE) and the new one (which uses JOIN keyword). Your query doesn't work because only latest table in FROM is visible to JOIN :

--Doesn't work 
SELECT a.ID, b.ID, c.ID, d.ID
FROM table1 a, table2 b, table3 c, table3 d
INNER JOIN database2.tablex tx ON a.ID = tx.ID 

--Works (note - table1 a is the last in "FROM"):
SELECT a.ID, b.ID, c.ID, d.ID
FROM  table2 b, table3 c, table3 d, table1 a 
INNER JOIN database2.tablex tx ON a.ID = tx.ID ;

-- Preferred way :
SELECT a.ID, b.ID, c.ID, d.ID
FROM  table1 a  
INNER JOIN table2 b ON (...)
INNER JOIN table3 c ON (...) 
INNER JOIN table3 d ON (...)
INNER JOIN database2.tablex tx ON a.ID = tx.ID

Upvotes: 4

Related Questions