Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Double Inner Join

    I have a table where 2 field in a table which are linked to 2 other different tables.

    I need to retrieve values from the 3 tables.
    Can I use twice the inner join ? like :

    select f_tb2,f_tb3,* from tb1
    inner join tb2 on tb2.f_id=tb1.an_id and
    inner join tb3 on tb3.f_id=tb1.another_id
    etc...

    Or something like that ?

    Or any other method ?

    Thanks for help.
    Pierre (Pl-Arts)

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Double Inner Join

    Originally posted by Plarde
    I have a table where 2 field in a table which are linked to 2 other different tables.

    I need to retrieve values from the 3 tables.
    Can I use twice the inner join ? like :

    select f_tb2,f_tb3,* from tb1
    inner join tb2 on tb2.f_id=tb1.an_id and
    inner join tb3 on tb3.f_id=tb1.another_id
    etc...

    Or something like that ?

    Or any other method ?

    Thanks for help.
    Yes you can join many columns in a column to any number of tables. Frequently there will be more than one column in a table which needs to correspond to other tables.

    When you start talking about outer joining a table to more than one table is when you may run into limitations, but in this case you will definitely be able to do that

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can do it, with one correction and one suggestion:

    select f_tb2, f_tb3, tb1.*
    from tb1
    inner join tb2 on tb2.f_id=tb1.an_id and
    inner join tb3 on tb3.f_id=tb1.another_id

    correction: remove the word "and" from between the joins

    suggestion: qualify the asterisk, otherwise it returns all columns from all tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2002
    Posts
    66
    here is a rather large query we use regularly with multiple joins:

    select item.item,
    Item.description,
    LocInv.Item_Desc as LIDesc,
    WOH.Item_Desc as WOHDesc,
    WOD.Item_desc as WODDesc,
    WI.Item_desc as WIDesc,
    BOMD.Item_desc as BOMDDesc,
    BOMH.Item_desc as BOMHDesc
    from Item with (nolock)
    inner join Location_inventory LocInv with (nolock)
    on Item.Item = LocInv.Item
    inner join Work_Order_Header WOH with (nolock)
    on Item.Item = WOH.Item
    inner join Work_Order_Detail WOD with (nolock)
    on Item.Item = WOD.Item
    inner join Work_Instruction WI with (nolock)
    on Item.Item = WI.Item
    inner join Bill_Of_Materials_Detail BOMD with (nolock)
    on Item.Item = BOMD.Item
    inner join Bill_Of_Materials_Header BOMH with (nolock)
    on Item.Item = BOMH.Item
    where (Item.Description <> LocInv.Item_Desc and Item.Item = LocInv.Item)
    or (Item.Description <> WOH.Item_Desc and Item.Item = WOH.Item)
    or (Item.Description <> WOD.Item_Desc and Item.Item = WOD.Item)
    or (Item.Description <> WI.Item_Desc and Item.Item = WI.Item)
    or (Item.Description <> BOMD.Item_Desc and Item.Item = BOMD.Item)
    or (Item.Description <> BOMH.Item_Desc and Item.Item = BOMH.Item)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •