Join problem

[Guest_imported]

Hello everyone,

I have the following SQL-statement:
select
t1.name, t1.identifier, t2.nummer, t2.identifier, t2.id
from
table1 t1, table2 t2, table3 t3
where
t1.t2 = t2.id;

This returns values like:
name identifier nummer identifier id
bla1 bla2 4711 bla3 1
bla4 bla5 0815 bla6 5
... ... ... ... ...

Now I want to perform an other SQL-statement inside this SQL-statement but with a returned value from upper statement e.g. in this case with the name-value 'bla1'...
I tried something like this:

select
t1.name, t1.identifier, t2.nummer, t2.identifier, t2.id
from
table1 t1, table2 t2, table3 t3,
( select *
from table4
where table4.name = t1.name -- for each returned name value a
-- new SQL-statement using this
-- value
) bla
where
t1.t2 = t2.id;

But when I run the statement I get the error ORA-00904 (I use Oracle 8.1.7)
Can anyone help me?

 

[r937]

you might have upper/lower confused with inner/outer

select table4.*
from table4,
( select
t1.name, t1.identifier
, t2.nummer, t2.identifier, t2.id
from
table1 t1, table2 t2, table3 t3
where
t1.t2 = t2.id ) bla
where table4.name = bla.name

any reason why you can't just add table 4 to the three-way join for a four-way?

rudy

http://rudy.ca/

 

[angiole]

Curiously, Table3 is not referenced in any of the joins.
Can you explain. AA 8~)

 

[r937]

good eye, AA, i completely missed that

i'd like to hear the explanation too

there are times when a cross join (which is what you get when you add a table to the FROM list and don't qualify which rows or how to join them) is useful, for example

Generate Test Data Quickly With Cross Joins

http://www.swynk.com/friends/boyle/jointestdata.asp

rudy