Re: join in SQL

Gerard Le Comte wrote in message <346182F1.1700_at_sns.fr>...
>Consider 3 tables : A, B and C.
>A contains a reference to C and a value :
> a.c_id = c.id
>B only contains a reference to A and C :
> b.c_id = c.id and b.a_id = a.id
>C only contains a value
>
>Consider now, that I want to know all group (a.value;c.value)
>and that B is empty.

I'm not too clear on exactly what you want.

If you do an inner join on two tables one of which has no rows then the result will be no rows.

Is this what you want?

create view D as
select a.value AV, c.value, AC ,c.id ID from a, b, c where
b.c_id = c.id and b.a_id = a.id;

Create view E as
select a.value AV, c.value, AC ,c.id ID from a, c where a.c_id = c.id

select AV, AC from D, E
where D.id(+) = E.id Received on Mon Nov 10 1997 - 00:00:00 CST