Abdul Rauf Abdul Rauf - 1 month ago 5
SQL Question

How to join a multiple foreign key within the foreign key in a single table in SQL Server 2014

I have Waybill Table in which the joborderID is foreign key of Joborder Table in joborder Table there is FleetTypeID which is the ForeignKey of FleetTypeID and in FleetType Table there are two more foreign key ItemsID and FleetTypeItemsID

The problem is I want to retrieve joborder_table data in waybill_table but the foreign keys in joborder_table are not joining


select DISTINCT FleetTypeItem as Fleet_Items, ItemsName as Items
from ItemsTbl,FleetTypeItemTbl
inner join FleetTypeTbl on FleetTypeItemTbl.FleetTypeItemID=FleetTypeTbl.FleetTypeItemID and
ItemsTbl.ItemsID=FleetTypeTbl.ItemsID


This is not running please assist me this is my project

Answer

You need to separate out your joins. Also, if those columns are present in multiple tables, you have to reference the table in your select so it knows where to look:

SELECT DISTINCT FleetTypeItemTbl.FleetTypeItem as Fleet_Items, ItemsTbl.ItemsName as Items 
FROM FleetTypeItemTbl
INNER JOIN FleetTypeTbl
ON FleetTypeItemTbl.FleetTypeItemID = FleetTypeTbl.FleetTypeItemID
INNER JOIN ItemsTbl
ON ItemsTbl.ItemsID = FleetTypeTbl.ItemsID

So, you SELECT something FROM else, INNER JOIN that ON something, then INNER JOIN that onto something else.

So, rather than say FROM 1, 2, and 3 ON these joins, say FROM 1, JOIN 1 = 2, JOIN 2 = 3. ...if that makes sense.

It might also make things more readable to alias your tables like this:

SELECT DISTINCT FTI.FleetTypeItem as Fleet_Items, I.ItemsName as Items 
FROM FleetTypeItemTbl FTI
INNER JOIN FleetTypeTbl FT
ON FTI.FleetTypeItemID = FT.FleetTypeItemID
INNER JOIN ItemsTbl I
ON I.ItemsID = FT.ItemsID
Comments