Re: Antw: Problem with joins

On Wed, 5 Jul 2000, Gerhard Dieringer wrote:

> Jean-Marc Libs wrote:
> >...
> >I have also tried:
> >select source_name,data_value from source,data where data_source_id=source_id union select source_name,source_id,NULL from source,data
> 
> >This is a bit better, in the sense that I get back all I need, but there
> >are too many lines: when there is data, I get the line with the data value
> >and also with NULL.
> >...
> 
> You are on the right way. Change your querry to 
> 
> select source_name,data_value 
> from source,data 
> where data_source_id=source_id 
> union 
> select source_name,source_id
> from source
> WHERE source_id NOT IN (SELECT source_id FROM data);
> 
> and you will get your expected result.
> 
> BTW this simulates an outer join.

Oh, I wasn't aware that outer joins aren't supported :-(

Well, I found that it does kinda work. My app is actually more
complicated than this, so now I have:

select source_name,data_value 
from source,data
where data_source_id=source_id and "conditions on source"
union
select source_name,NULL
from source
WHERE source_id NOT IN (SELECT data_source_id FROM data where "my conditions on data")

I get exactly the rows I wanted, and I am very glad for the tip (that
should go in a FAQ, it it isn't already).

But now I wonder where I could put my 'order by source_order' statement,
as I can't figure out how to put the rows in the proper order :-(

Thanks for the prompt answer anyway,
Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURG    http://www.actimage.net
Professionnel : jeanmarc@actimage.fr 
Lieu de travail : jml@obs.coe.int