Thread: join if there, blank if not
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. -----------------------------------------
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. > ----------------------------------------- >
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. > ----------------------------------------- >
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)
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)