Thread: Problem with joins

Hi all,

I am trying to do something unusual (for me) and I can't quite
find the relevant part in the documentation (no mention to joins
in the insert section, or the index).

I want to get a list of data sources with the corresponding data,
if the data exists, or with null, if the data doesn't. But anyway
I need the data sources (all of them).

So I have tried:
select source_name,data_value from source,data where data_source_id=source_id

but it lacks those rows where there is no data in "data" table.

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
fromsource,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.

I strongly suspect that there is a more elegant way of doing it:
a solution or pointers to relevant online litterature would be welcome.

Thanks,
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

> I want to get a list of data sources with the corresponding data,
> if the data exists, or with null, if the data doesn't. But anyway
> I need the data sources (all of them).

You want an outer join. Postgres doesn't have that yet, but you can
mimic it.

> 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.

Close. Try
 select source_name,data_value  from source,data where data_source_id=source_id union select source_name,source_id,NULL
fromsource  where source_id not in (select data_source_id from data);
 
                        - Thomas