Re: join in SQL

Gerard,

The reason your queries return no rows is that you have not included a join to table B, therefore what the query will do is join table A to table C (a.c_id = c.id) and then attempt to join this result to all row in B. As B has no rows, no rows are returned. Also if B contained many rows you would get a cartesian join and return a row for every row contained in B.

After that preamble the answer to your query lies in something called an outer join which can be applied to a table which may not satisfy some join criteria, which is clearly the case when the table conatins no rows. To implement this you must add (+) follwing the join criteria applying to the table deficient in rows.

e.g. in your example

select a.value AV,

           c.value AC
from a, b, c
where a.c_id = c.id
and a.id = b.a_id(+);

should return the desired rows.

Note that there is a limitation in that a table may be outer joined to at most one other table.

Hope this helps,

Ian Received on Fri Nov 07 1997 - 00:00:00 CST