Is it possible to Inner Join 3 tables?

[csiwa28]

Is it possible to Inner Join 3 tables? If so, can anyone give me a general format that I could try to follow?

 

[vasah20]

here's a quick example:

select * from table1 inner join (table2 inner join table3 on table2.field = table3.field) derivedA on table1.field = derivedA.field

you don't have to fully qualify the names, you can just use aliases and what not like i did

hth
leo

 

[csiwa28]

Where does derivedA come from?

 

[vasah20]

derivedA is a derived table... so it comes from the result of (table2 inner join table3 on table2.field = table3.field).


The way SQL works is that it will return tables, and you can use those tables to query from. Here's a shorter example:

select der.field1, der.field2 from (SELECT field1, field2, FROM myTbl) der

this is the same thing as
select field1, field2 from myTbl

sorta following?
the thing with derived tables, is that you can only access the fields that you explicitly call. So let's say there's a field3 that you want to order by:

select der.field1, der.field2 from (SELECT field1, field2, FROM myTbl) der order by der.field3

this doesn't work because you need to include field3 in your subquery... armed with that, let's try this:

select der.field1, der.field2 from (SELECT field1, field2, field3 FROM myTbl) der order by der.field3

that should work.

hth
leo