pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Antw: Problem with joins
2+ messages / 2 participants
[nested] [flat]
* Re: Antw: Problem with joins
@ 2000-06-05 13:48 Jean-Marc Libs <jean-marc.libs@obs.coe.int>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 0 replies; 2+ messages in thread
From: Jean-Marc Libs @ 2000-06-05 13:48 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: 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@actimage.fr
Lieu de travail : jml@obs.coe.int
^ permalink raw reply [nested|flat] 2+ messages in thread
* Antw: Problem with joins
@ 2000-07-05 10:31 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 1 reply; 2+ messages in thread
From: Gerhard Dieringer @ 2000-07-05 10:31 UTC (permalink / raw)
To: jean-marc.libs@obs.coe.int; +Cc: pgsql-sql
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.
Gerhard
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-07-05 10:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-05 10:31 Antw: Problem with joins Gerhard Dieringer <DieringG@eba-haus.de>
2000-06-05 13:48 ` Jean-Marc Libs <jean-marc.libs@obs.coe.int>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox