On Sun, May 4, 2008 at 11:28 AM, seiliki <
seiliki@so-net.net.tw> wrote:
Hi!
I expect the SELECT to return two rows. Would some kind
soul explain for me why it gives only one row?
TIA
CN
=============
CREATE TABLE x(c1 text,c2 int2);
INSERT INTO x VALUES('a',10);
INSERT INTO x VALUES('b',NULL);
CREATE TABLE y(c1 int2,c2 int2,c3 text);
INSERT INTO y VALUES(10,9,'yyy');
CREATE TABLE z(c1 text,c2 text);
INSERT INTO z VALUES('a','zzz');
INSERT INTO z VALUES('b','zzzz');
SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
LEFT OUTER JOIN y ON (x.c2=y.c1)
WHERE y.c2=9;
c1 | c3 | c2
----+-----+-----
a | yyy | zzz
Your where clause is filtering out the values. On the second record in X, y.c2 is NULL so to get 2 rows you would need to write:
SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
LEFT OUTER JOIN y ON (x.c2=y.c1)
WHERE y.c2=9 OR y.c2 IS NULL;
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com==================================================================