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.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 :)|||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|||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)

double inner join

select a.xxxx, b.xxxxxx, d.xxxxxx
a inner join b where y=z
c inner join d where y=q.
what do these two inner joins do in simple words?
will this be the sum of the two inner joins? As if these were both separate
inner joins and the rows that match are basically appended to the rows that
match of the other. thank you. -hazzEach Join is "Joining" One 'resultset' to another 'resultset'.
a 'resultset' is either a table, or the results of joining ywo other tables
or resultsets.
So if you want to 'join' 4 tables say A, B, C, and D,
you can do it in a variety of ways. I'll put parentheses to show the order
of operations, (omitting Join conditions for clarity (On clauses)
From ( (A Join B ) Join C ) Join D -- This is default order,
without parentheses,
From A Join B Join C Join D
it would do the same,
1) Join A to B
2) Join C with the result of step 1
3) Join D with the resultset from step 2
or, you could...
From (A Join B)
Join
(C Join D)
which would be closest to what you were trying to do,
1) Join A to B
2) Join C to D
3 Join result from Step 1 to result from Step 2
"Hazz" wrote:

> select a.xxxx, b.xxxxxx, d.xxxxxx
> a inner join b where y=z
> c inner join d where y=q.
> what do these two inner joins do in simple words?
> will this be the sum of the two inner joins? As if these were both separat
e
> inner joins and the rows that match are basically appended to the rows tha
t
> match of the other. thank you. -hazz
>
>|||Also, Join conditions must use the 'On' Keyword, not 'Where' for
conditions, so to summarize, your sql should be
Select *
From (A Join B On B.y = A.z)
Join
(C Join D On D.y = C.q)
On C.q = A.q
"Hazz" wrote:

> select a.xxxx, b.xxxxxx, d.xxxxxx
> a inner join b where y=z
> c inner join d where y=q.
> what do these two inner joins do in simple words?
> will this be the sum of the two inner joins? As if these were both separat
e
> inner joins and the rows that match are basically appended to the rows tha
t
> match of the other. thank you. -hazz
>
>|||Thank you very much for your ideas. . thx -greg
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:020CB179-05D2-4D56-9815-2CF867A29A04@.microsoft.com...
> Also, Join conditions must use the 'On' Keyword, not 'Where' for
> conditions, so to summarize, your sql should be
> Select *
> From (A Join B On B.y = A.z)
> Join
> (C Join D On D.y = C.q)
> On C.q = A.q
> "Hazz" wrote:
>