From: | "Jean-Marc Libs" <jean-marc(dot)libs(at)obs(dot)coe(dot)int> |
---|---|
To: | Gerhard Dieringer <DieringG(at)eba-haus(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Antw: Problem with joins |
Date: | 2000-06-05 13:48:30 |
Message-ID: | Pine.LNX.4.21.0006051522390.29725-100000@centaure.obs.coe.int |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)actimage(dot)fr
Lieu de travail : jml(at)obs(dot)coe(dot)int
From | Date | Subject | |
---|---|---|---|
Next Message | p.lam | 2000-06-05 15:05:57 | CREATE FUNCTION- Table as argument |
Previous Message | Poet/Joshua Drake | 2000-06-05 13:25:23 | [OT] Book on Postgres (Not a question) |