CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Salem
Salem

Reputation: 87

Inner Join in stored procedure

I have this stored procedure:

ALTER PROCEDURE [dbo].[Get_All_Items]
AS
BEGIN
    SELECT
        ItemID, ItemName, CategoryID, CountryID,
        ItemSize, ColorID, ProductionDate, ExpiryDate, 
        UnitName, FirstUnitBarcode, FirstItemQuantity, 
        FirstUnitDefult, FirstUnitLimit, 
        UnitsTbl.SecondUnit, SecondUnitBarcode, SecondItemQuantity, 
        SecondUnitDefult, SecondUnitLimit, 
        ThirdUnit, ThirdUnitBarcode, ThirdItemQuantity, 
        ThirdUnitDefult, ThirdUnitLimit, UnitDefult, 
        ItemImage, ItemStatus, ItemsMainTbl.Nots,
        ItemsMainTbl.CreatedBy, ItemsMainTbl.CreatedDate, 
        ItemsMainTbl.ModifiedBy, ItemsMainTbl.ModifiedDate 
    FROM 
        ItemsMainTbl  
    INNER JOIN
        UnitsTbl ON ItemsMainTbl.FirstUnit = UnitsTbl.UnitID 
    INNER JOIN
        UnitsTbl ON ItemsMainTbl.SecondUnit = UnitsTbl.UnitID 
END

Conditions:

How to join all three tables on that common column?

I got only the first one 

ItemsMainTbl.FirstUnit = UnitsTbl.UnitID

Thanks

Upvotes: 0

Views: 4020

Answers (2)

Max Szczurek
Max Szczurek

Reputation: 4334

If you want to join the same table multiple times, you need to specify an alias for each instance of the joined table, like this:

SELECT *
FROM ItemsMainTbl  
INNER JOIN UnitsTbl u1 ON ItemsMainTbl.FirstUnit = u1.UnitID 
INNER JOIN UnitsTbl u2 ON ItemsMainTbl.SecondUnit = u2.UnitID 
INNER JOIN UnitsTbl u3 ON ItemsMainTbl.ThirdUnit = u3.UnitID

Then in the select statement, you can differentiate between the joined instances, like this:

SELECT u1.Barcode, -- Barcode from FirstUnit
       u2.Barcode, -- Barcode from SecondUnit
       u3.Barcode  -- Barcode from ThirdUnit

Upvotes: 1

Shivam Aggarwal
Shivam Aggarwal

Reputation: 131

You need to define reference for you UnitsTbl. Think of these references as new objects of same class, in terms of OOP. Also, it is a good habit to use references for whenever querying over a table. 

SELECT *
FROM 
    ItemsMainTbl t1
INNER JOIN
    UnitsTbl u1 ON t1.FirstUnit = u1.UnitID 
INNER JOIN
    UnitsTbl u2 ON t1.SecondUnit = u2.UnitID 
INNER JOIN 
    UnitsTbl u3 ON t1.ThirdUnit = u3.UnitID

Upvotes: 0

Related Questions