Re: join in SQL

Gerard Le Comte wrote:
>
> Hi,
>
> I have a question on table joins :
>
> 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.
>
> ----
> select a.value AV, c.value, AC from a, b, c
> where
> ((a.c_id = c.id)
> OR (b.c_id = c.id and b.a_id = a.id)
> );
> ----
>
> the result is always "no row selected" even if this
> request don't return "no row.." :
> ----
> select a.value AV, c.value, AC from a, c
> where
> a.c_id = c.id;
>
> ----
> I known that a good request could be :
>
> select a.value AV, c.value, AC from a, b, c
> where
> b.c_id = c.id and b.a_id = a.id;
> UNION ALL
> select a.value AV, c.value, AC from a, c
> where a.c_id = c.id;
> ---
> But, it is not a simply way to build a more complex request.
>
> 1) How could we build this request as the first request
> without "UNION ALL" ?
> 2) Why the following request returns "no row selected" : ?
> --
> select * from a, b; -- a is not empty, b is empty
> --
>
> thanks for help.
>
> Gerard Le Comte

Hi Gerard,

if table b is empty you'll always get no rows selected if you don't use the union.

select * from a, b; -- a is not empty, b is empty

returns no rows 'cause the cartesion product of <something> X NULL is NULL   select a.value AV, c.value, AC from a, b, c   where
 ((a.c_id = c.id)
  OR (b.c_id = c.id and b.a_id = a.id)
 );

returns no rows 'cause <something> OR NULL is NULL

-- 
Regards

M.Gresz    :-)