CODEWITHSUNDEEP
sql-server
Dan Cundy
Dan Cundy

Reputation: 2849

Join two tables on matching ID?

Question

I have a query and would like to join one column from another table based on its matching ID

Query So far

SELECT DENumber, AcquiredDate, ItemDescription, ItemName, LocationID FROM dbo.Assets JOIN LocationName FROM dbo.Locations on ProductID             
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 6

The matching ID in both tables is LocationID

Problem

My query is wrong and throws errors

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

Upvotes: 0

Views: 99

Answers (2)

mohan111
mohan111

Reputation: 8865

SELECT L.DENumber, A.AcquiredDate, A.ItemDescription, A.ItemName, L.LocationID
 FROM dbo.Assets A JOIN 
  ON dbo.Locations L on 
 L.ProductID  = A.ProductID        
WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate()) >= 6

Upvotes: 0

Dan
Dan

Reputation: 10680

Rewrite your query to this:

SELECT DENumber, AcquiredDate, ItemDescription, ItemName, LocationName
FROM dbo.Assets INNER JOIN dbo.Locations ON Assets.LocationId = Locations.LocationId            
WHERE DATEDIFF(YEAR, AcquiredDate, GetDate()) >= 6

To clarify: In a JOIN, you must specify the names of both tables on either side of the JOIN keyword. Then, you specify the join criteria after the ON keyword:

FROM <<table1>> INNER JOIN <<table2>> ON <<join criteria>>

Here, we only want records that exist in both tables, which is why we use the INNER join, but you can also take all records from either the LEFT table, the RIGHT table or BOTH tables. In that case, you would do a LEFT JOIN, RIGHT JOIN our OUTER JOIN respectively.

Upvotes: 3

Related Questions