pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: join if there, blank if not
5+ messages / 4 participants
[nested] [flat]
* join if there, blank if not
@ 2000-07-12 15:34 Gary Stainburn <gary.stainburn@ringways.co.uk>
2000-07-12 14:41 ` Re: join if there, blank if not Jacques Williams <jacques@jacqro.COM>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2000-07-12 15:34 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I just can't get my head round this one so I hope one of you can.
I've got two tables, one holding phone calls, and another holding phone numbers.
I want to do a select where if the number in the calls table exists
in the numbers table, the description is included otherwise the
description field is blank.
Unfortunately, using the select I've tried, if the number is not on
the one of the tables, it's ignored.
Calls table
cdate date
ctime time
cextn char(3)
cnumber x(12)
Numbers table
nnumber x(12)
ndesc x(30)
Select I tried.
select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
from calls c, numbers n where c.cnumber = n.nnumber;
-----------------------------------------
Gary Stainburn.
Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
-----------------------------------------
Murphy's Laws: (327) The minute before the engineer arrives, the printer starts working.
-----------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join if there, blank if not
2000-07-12 15:34 join if there, blank if not Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2000-07-12 14:41 ` Jacques Williams <jacques@jacqro.COM>
0 siblings, 0 replies; 5+ messages in thread
From: Jacques Williams @ 2000-07-12 14:41 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary,
What you want here is an outer join. The syntax would look something like this:
select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
from calls c, numbers n
where c.cnumber=n.nnumber
union all
select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
from numbers;
(I haven't tried this, but it should work.) For more information on outer joins, see Bruce Momjian's book at http://www.postgresql.org/docs/aw_pgsql_book/ .
Jacques Williams
On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> Hi all,
>
> I just can't get my head round this one so I hope one of you can.
>
> I've got two tables, one holding phone calls, and another holding phone numbers.
>
> I want to do a select where if the number in the calls table exists
> in the numbers table, the description is included otherwise the
> description field is blank.
>
> Unfortunately, using the select I've tried, if the number is not on
> the one of the tables, it's ignored.
>
> Calls table
> cdate date
> ctime time
> cextn char(3)
> cnumber x(12)
>
> Numbers table
>
> nnumber x(12)
> ndesc x(30)
>
> Select I tried.
>
> select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> from calls c, numbers n where c.cnumber = n.nnumber;
>
> -----------------------------------------
> Gary Stainburn.
> Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
> REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
> -----------------------------------------
> Murphy's Laws: (327) The minute before the engineer arrives, the printer starts working.
> -----------------------------------------
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: join if there, blank if not
@ 2000-07-12 18:51 Henry Lafleur <HLafleur@phoenixforge.com>
2000-07-13 10:59 ` Re: join if there, blank if not Patrick Jacquot <patrick.jacquot@anpe.fr>
0 siblings, 1 reply; 5+ messages in thread
From: Henry Lafleur @ 2000-07-12 18:51 UTC (permalink / raw)
To: Jacques Williams <jacques@jacqro.COM>; Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Jacques,
The problem with using the union in this way is that you get NULLs for a
number weather or not it has an associated record in calls.
To do a pure outer join, it would be something like this:
select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
from calls c, numbers n
where c.cnumber=n.nnumber
union all
select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
from numbers
WHERE nnumber NOT IN (SELECT nnumber FROM calls);
---
What I have always had trouble with, though, is if you have multiple fields
for a primary key. For example, if a customer master table also had ship-to
locations as the key and you wanted to get all customers and any orders for
that customer, in rough ANSI SQL it would be:
SELECT c.cust_number, c.ship_to, o.item
FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
c.ship_to = o.ship_to
then, in the union, it is not clear how to do it:
SELECT c.cust_number, c.ship_to, o.item
FROM cust c, orders o
WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
UNION
SELECT cust_number, ship_to, NULL AS item
FROM cust
WHERE ???
which I never know what to do at ??? -
WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
is one choice, but this doesn't help if the ship to doesn't match. We can
get wild and try -
WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
ship_to = cust.ship_to)
but if you go to two and three keys, what happens then? It seems like it
should work if we continue. But how efficiently does this work?
Has anyone examined this problem?
Thanks,
Henry
-----Original Message-----
From: Jacques Williams [mailto:jacques@jacqro.COM]
Sent: Wednesday, July 12, 2000 9:41 AM
To: Gary Stainburn
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] join if there, blank if not
Gary,
What you want here is an outer join. The syntax would look something like
this:
select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
from calls c, numbers n
where c.cnumber=n.nnumber
union all
select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
from numbers;
(I haven't tried this, but it should work.) For more information on outer
joins, see Bruce Momjian's book at
http://www.postgresql.org/docs/aw_pgsql_book/ .
Jacques Williams
On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> Hi all,
>
> I just can't get my head round this one so I hope one of you can.
>
> I've got two tables, one holding phone calls, and another holding phone
numbers.
>
> I want to do a select where if the number in the calls table exists
> in the numbers table, the description is included otherwise the
> description field is blank.
>
> Unfortunately, using the select I've tried, if the number is not on
> the one of the tables, it's ignored.
>
> Calls table
> cdate date
> ctime time
> cextn char(3)
> cnumber x(12)
>
> Numbers table
>
> nnumber x(12)
> ndesc x(30)
>
> Select I tried.
>
> select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> from calls c, numbers n where c.cnumber = n.nnumber;
>
> -----------------------------------------
> Gary Stainburn.
> Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
> REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
> -----------------------------------------
> Murphy's Laws: (327) The minute before the engineer arrives, the printer
starts working.
> -----------------------------------------
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join if there, blank if not
2000-07-12 18:51 RE: join if there, blank if not Henry Lafleur <HLafleur@phoenixforge.com>
@ 2000-07-13 10:59 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
0 siblings, 0 replies; 5+ messages in thread
From: Patrick Jacquot @ 2000-07-13 10:59 UTC (permalink / raw)
To: pgsql-sql
Henry Lafleur wrote:
> Jacques,
>
> The problem with using the union in this way is that you get NULLs for a
> number weather or not it has an associated record in calls.
>
> To do a pure outer join, it would be something like this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
> from numbers
> WHERE nnumber NOT IN (SELECT nnumber FROM calls);
>
> ---
>
> What I have always had trouble with, though, is if you have multiple fields
> for a primary key. For example, if a customer master table also had ship-to
> locations as the key and you wanted to get all customers and any orders for
> that customer, in rough ANSI SQL it would be:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
> c.ship_to = o.ship_to
>
> then, in the union, it is not clear how to do it:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c, orders o
> WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
> UNION
> SELECT cust_number, ship_to, NULL AS item
> FROM cust
> WHERE ???
>
> which I never know what to do at ??? -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
> is one choice, but this doesn't help if the ship to doesn't match. We can
> get wild and try -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
> ship_to = cust.ship_to)
> but if you go to two and three keys, what happens then? It seems like it
> should work if we continue. But how efficiently does this work?
>
> Has anyone examined this problem?
>
> Thanks,
>
> Henry
>
> -----Original Message-----
> From: Jacques Williams [mailto:jacques@jacqro.COM]
> Sent: Wednesday, July 12, 2000 9:41 AM
> To: Gary Stainburn
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] join if there, blank if not
>
> Gary,
>
> What you want here is an outer join. The syntax would look something like
> this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
> from numbers;
>
> (I haven't tried this, but it should work.) For more information on outer
> joins, see Bruce Momjian's book at
> http://www.postgresql.org/docs/aw_pgsql_book/ .
>
> Jacques Williams
>
>
> On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> > Hi all,
> >
> > I just can't get my head round this one so I hope one of you can.
> >
> > I've got two tables, one holding phone calls, and another holding phone
> numbers.
> >
> > I want to do a select where if the number in the calls table exists
> > in the numbers table, the description is included otherwise the
> > description field is blank.
> >
> > Unfortunately, using the select I've tried, if the number is not on
> > the one of the tables, it's ignored.
> >
> > Calls table
> > cdate date
> > ctime time
> > cextn char(3)
> > cnumber x(12)
> >
> > Numbers table
> >
> > nnumber x(12)
> > ndesc x(30)
> >
> > Select I tried.
> >
> > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> > from calls c, numbers n where c.cnumber = n.nnumber;
> >
> > -----------------------------------------
> > Gary Stainburn.
> > Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
> > REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
> > -----------------------------------------
> > Murphy's Laws: (327) The minute before the engineer arrives, the printer
> starts working.
> > -----------------------------------------
> >
hi
for multiple fields in the join condition, i always succeeded wit a
WHERE NOT EXISTS SELECT ...
subselect in the second part of the UNION.
Hoing that may help (although perhaps suboptimal)
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: join if there, blank if not
@ 2000-07-13 13:11 Henry Lafleur <HLafleur@phoenixforge.com>
0 siblings, 0 replies; 5+ messages in thread
From: Henry Lafleur @ 2000-07-13 13:11 UTC (permalink / raw)
To: Patrick Jacquot <patrick.jacquot@anpe.fr>; pgsql-sql
Patrick, Thanks. For some reason, I was typing EXIST and it wasn't
working--it's EXISTS! (OOPS--old habits)
I was talking to Jacques about this. After comparing performance using NOT
EXISTS, NOT IN, and even UNION vs. LEFT OUTER JOIN on an MS SQL 7 Server,
they all run at about the same speed.
(Interestingly, the MS SQL server did a similar query in 30 seconds to what
took 90 seconds on the psql server. The MS SQL server is a 500MHz dual PIII
with 1 GB of RAM and a RAID-5 with 4 9GB SCSI drives. The PSQL server is a
Linux box busy doing many things with a 166 MHz Pentium MMX, 96 MB of RAM,
and a slow IDE HD.)
Henry
-----Original Message-----
From: Patrick Jacquot [mailto:patrick.jacquot@anpe.fr]
Sent: Thursday, July 13, 2000 5:59 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] join if there, blank if not
Henry Lafleur wrote:
> Jacques,
>
> The problem with using the union in this way is that you get NULLs for a
> number weather or not it has an associated record in calls.
>
> To do a pure outer join, it would be something like this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
> from numbers
> WHERE nnumber NOT IN (SELECT nnumber FROM calls);
>
> ---
>
> What I have always had trouble with, though, is if you have multiple
fields
> for a primary key. For example, if a customer master table also had
ship-to
> locations as the key and you wanted to get all customers and any orders
for
> that customer, in rough ANSI SQL it would be:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
> c.ship_to = o.ship_to
>
> then, in the union, it is not clear how to do it:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c, orders o
> WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
> UNION
> SELECT cust_number, ship_to, NULL AS item
> FROM cust
> WHERE ???
>
> which I never know what to do at ??? -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
> is one choice, but this doesn't help if the ship to doesn't match. We can
> get wild and try -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
> ship_to = cust.ship_to)
> but if you go to two and three keys, what happens then? It seems like it
> should work if we continue. But how efficiently does this work?
>
> Has anyone examined this problem?
>
> Thanks,
>
> Henry
>
> -----Original Message-----
> From: Jacques Williams [mailto:jacques@jacqro.COM]
> Sent: Wednesday, July 12, 2000 9:41 AM
> To: Gary Stainburn
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] join if there, blank if not
>
> Gary,
>
> What you want here is an outer join. The syntax would look something like
> this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
> from numbers;
>
> (I haven't tried this, but it should work.) For more information on outer
> joins, see Bruce Momjian's book at
> http://www.postgresql.org/docs/aw_pgsql_book/ .
>
> Jacques Williams
>
>
> On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> > Hi all,
> >
> > I just can't get my head round this one so I hope one of you can.
> >
> > I've got two tables, one holding phone calls, and another holding phone
> numbers.
> >
> > I want to do a select where if the number in the calls table exists
> > in the numbers table, the description is included otherwise the
> > description field is blank.
> >
> > Unfortunately, using the select I've tried, if the number is not on
> > the one of the tables, it's ignored.
> >
> > Calls table
> > cdate date
> > ctime time
> > cextn char(3)
> > cnumber x(12)
> >
> > Numbers table
> >
> > nnumber x(12)
> > ndesc x(30)
> >
> > Select I tried.
> >
> > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> > from calls c, numbers n where c.cnumber = n.nnumber;
> >
> > -----------------------------------------
> > Gary Stainburn.
> > Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
> > REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
> > -----------------------------------------
> > Murphy's Laws: (327) The minute before the engineer arrives, the printer
> starts working.
> > -----------------------------------------
> >
hi
for multiple fields in the join condition, i always succeeded wit a
WHERE NOT EXISTS SELECT ...
subselect in the second part of the UNION.
Hoing that may help (although perhaps suboptimal)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-07-13 13:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-12 15:34 join if there, blank if not Gary Stainburn <gary.stainburn@ringways.co.uk>
2000-07-12 14:41 ` Jacques Williams <jacques@jacqro.COM>
2000-07-12 18:51 RE: join if there, blank if not Henry Lafleur <HLafleur@phoenixforge.com>
2000-07-13 10:59 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
2000-07-13 13:11 RE: join if there, blank if not Henry Lafleur <HLafleur@phoenixforge.com>
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