Search code examples
sqlinner-join

SQL inner join 3 tables with same key

I want to try to join 3 tables together they all use the same key called ITEM_NO item number. I might actually need to add one more table as well called "USR_MAG_ITEM_EXP" would that also be a inner join?

Tables are dbo.IM_PRC, dbo.IM_ITEM, dbo.IM_INV_CELL sorry for being a noob!

SELECT     dbo.IM_PRC.ITEM_NO, dbo.IM_PRC.LOC_ID, dbo.IM_PRC.DIM_1_UPR, 
dbo.IM_PRC.DIM_2_UPR,dbo.IM_PRC.DIM_3_UPR, dbo.IM_PRC.REG_PRC,dbo.IM_PRC.PRC_1, dbo.IM_PRC.PRC_2, dbo.IM_ITEM.CATEG_COD, dbo.IM_ITEM.SUBCAT_COD, dbo.IM_ITEM.STAT, dbo.IM_ITEM.ITEM_VEND_NO, dbo.IM_ITEM.IS_ECOMM_ITEM, dbo.IM_ITEM.VEND_ITEM_NO,dbo.IM_ITEM.LST_COST,dbo.IM_ITEM.USER_MAG_NAME,  dbo.IM_INV_CELL.LOC_ID,dbo.IM_INV_CELL.DIM_1_UPR, dbo.IM_INV_CELL.DIM_2_UPR, dbo.IM_INV_CELL.DIM_3_UPR, dbo.IM_INV_CELL.MIN_QTY,dbo.IM_INV_CELL.MAX_QTY, dbo.IM_INV_CELL.QTY_ON_HND,   


 FROM dbo.IM_PRC 
 INNER JOIN dbo.IM_ITEM ON dbo.IM_PRC.ITEM_NO = dbo.IM_ITEM.ITEM_NO 
 INNER JOIN dbo.IM_INV_CELL ON dbo.IM_INV_CELL.ITEM_NO = dbo.IM_PRC.ITEM_NO


    WHERE     (dbo.IM_ITEM.ITEM_VEND_NO = 'tum')

Thanks!!!

Solution

  • You have to repeat JOIN for each separate table:

    SELECT *
FROM dbo.IM_PRC 
INNER JOIN dbo.IM_ITEM ON dbo.IM_PRC.ITEM_NO = dbo.IM_ITEM.ITEM_NO 
INNER JOIN dbo.IM_INV_CELL ON dbo.IM_INV_CELL.ITEM_NO = dbo.IM_PRC.ITEM_NO
WHERE     (dbo.IM_ITEM.ITEM_VEND_NO = 'X')

    I recommend using aliases and removing unnecessary parentheses too:

    SELECT *
FROM dbo.IM_PRC P
INNER JOIN dbo.IM_ITEM I ON P.ITEM_NO = I.ITEM_NO 
INNER JOIN dbo.IM_INV_CELL IC ON IC.ITEM_NO = P.ITEM_NO
WHERE I.ITEM_VEND_NO = 'X'