Received: from eba-haus.de ([149.228.241.193]) by hub.org (8.10.1/8.10.1) with SMTP id e6D72FW00531 for ; Thu, 13 Jul 2000 03:02:15 -0400 (EDT) Received: from kopo001 by eba-haus.de;Thu, 13 Jul 2000 07:01:51 GMT with Novell_GroupWise; Thu, 13 Jul 2000 09:01:52 +0200 Message-Id: X-Mailer: Novell GroupWise 5.5.2 Date: Thu, 13 Jul 2000 09:01:44 +0200 From: "Gerhard Dieringer" To: "Henry Lafleur" Cc: "<" Subject: Antw: RE: join if there, blank if not Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id e6D72IW00547 X-Archive-Number: 200007/140 Henry Lafleur wrote: > ... > 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 ??? > ... I don't see any problems with multiple fields. See the following example: Outer join: SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt FROM tab_a LEFT JOIN tab_b ON (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1); Simulated outer join: SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, tab_b.b_txt FROM tab_a , tab_b WHERE (tab_a.k2 = tab_b.k2) AND (tab_a.k1 = tab_b.k1) UNION SELECT tab_a.k1, tab_a.k2, tab_a.a_txt, NULL FROM tab_a WHERE (tab_a.k1 NOT IN (SELECT tab_b.k1 FROM tab_b)) OR (tab_a.k2 NOT IN (SELECT tab_b.k2 FROM tab_b)); Gerhard