From: | Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> |
---|---|
To: | jml(at)obs(dot)coe(dot)int |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem with joins |
Date: | 2000-07-05 14:34:40 |
Message-ID: | 39634780.17592BFF@alumni.caltech.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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 from source
where source_id not in (select data_source_id from data);
- Thomas
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2000-07-05 14:51:11 | Re: Aww, sorry (last day of month) |
Previous Message | tjk@tksoft.com | 2000-07-05 13:00:35 | Re: GROUP by finish&&last day of month |