First, here's the SQL query that performs an inner join on dbo.IM_PRC, dbo.IM_ITEM, and dbo.IM_INV_CELL using ITEM_NO as the common key, including all the fields you specified:

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
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_PRC.ITEM_NO = dbo.IM_INV_CELL.ITEM_NO;

Can USR_MAG_ITEM_EXP be added with an inner join? #

Yes, you can absolutely use an inner join for USR_MAG_ITEM_EXP—but only if every ITEM_NO present in the combined result of the first three tables has a matching entry in USR_MAG_ITEM_EXP.

Inner joins only return rows where there's a matching ITEM_NO across all joined tables. If some ITEM_NOs from the initial three tables don't exist in USR_MAG_ITEM_EXP, those rows will be excluded from the final result. If that's exactly the behavior you want (only keep records that have data in all four tables), then inner join is the right choice.

If you need to retain all rows from the initial three tables even when there's no match in USR_MAG_ITEM_EXP, you'd use a LEFT JOIN instead. But based on your question about whether inner join is possible—yes, it's valid as long as the matching condition aligns with your desired outcome.

Here's how you'd modify the query to include USR_MAG_ITEM_EXP with an inner join (assuming it also uses ITEM_NO as the key):

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,
    -- Add any fields you need from USR_MAG_ITEM_EXP here
    USR_MAG_ITEM_EXP.YOUR_FIELD_NAME
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_PRC.ITEM_NO = dbo.IM_INV_CELL.ITEM_NO
INNER JOIN 
    USR_MAG_ITEM_EXP ON dbo.IM_PRC.ITEM_NO = USR_MAG_ITEM_EXP.ITEM_NO;

Just replace YOUR_FIELD_NAME with the actual columns you need from USR_MAG_ITEM_EXP.

内容的提问来源于stack exchange，提问作者johnny