Antw: RE: join if there, blank if not
От | Gerhard Dieringer |
---|---|
Тема | Antw: RE: join if there, blank if not |
Дата | |
Msg-id | s96d8580.045@kopo001 обсуждение исходный текст |
Список | pgsql-sql |
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.k2FROM tab_b)); Gerhard
В списке pgsql-sql по дате отправления: