pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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