Re: join in SQL

tkyte wrote :
> select a.value, c.value
> from a, c
> where a.c_id = c.id
> and not exists ( select NULL
> from B
> where b.c_id = c.id and b.a_id = a.id )
> /
>
> is one way to do it I believe...

A real case could be like that :
(select Man, house from Men(a),Houses(c) : this default house and his other houses (b))

This was exactly :

--
select a.value, c.value
  from a, c
 where a.c_id = c.id
   or exists ( select 'X'   from B
               where b.c_id = c.id and b.a_id = a.id );
--
	Yes, it works. Finally, it was better than an outer join
because, I have other tables like b and creating views for each
is not simple.

	But,.....
I have compared this method ( using the 'exists' function )
with this method :
	I have inserted a row in b : (null, null) so that a can do
the following SQL statement :

--
select a.value, c.value
  from a, b, c
 where a.c_id = c.id
   or ( b.c_id = c.id and b.a_id = a.id );
--

This return the same result but very, very faster. <<<*****************

However this method is not pretty and even if the table, b,
can't contain value (null, null), b contains an extra value.

Has anybody any comments on my speed results
or has others solutions ???

		thanks,

		Gerard Le Comte