Re: LEFT OUTER JOIN question

------=_Part_18927_8737542.1209919577546
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, May 4, 2008 at 11:28 AM, seiliki <seiliki@[EMAIL PROTECTED]
> 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.com
http://codeelixir.com
==================================================================

------=_Part_18927_8737542.1209919577546
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, May 4, 2008 at 11:28 AM, seiliki &lt;<a
href="mailto:seiliki@[EMAIL PROTECTED]
">seiliki@[EMAIL PROTECTED]
>&gt;
wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.8ex; padding-left: 1ex;">
Hi!<br>
<br>
I expect the SELECT to return two rows. Would some kind<br>
soul explain for me why it gives only one row?<br>
<br>
TIA<br>
<br>
CN<br>
=============<br>
CREATE TABLE x(c1 text,c2 int2);<br>
INSERT INTO x VALUES(&#39;a&#39;,10);<br>
INSERT INTO x VALUES(&#39;b&#39;,NULL);<br>
<br>
CREATE TABLE y(c1 int2,c2 int2,c3 text);<br>
INSERT INTO y VALUES(10,9,&#39;yyy&#39;);<br>
<br>
CREATE TABLE z(c1 text,c2 text);<br>
INSERT INTO z VALUES(&#39;a&#39;,&#39;zzz&#39;);<br>
INSERT INTO z VALUES(&#39;b&#39;,&#39;zzzz&#39;);<br>
<br>
SELECT x.c1,y.c3,z.c2<br>
FROM x JOIN z USING (c1)<br>
 &nbsp;LEFT OUTER JOIN y ON (x.c2=y.c1)<br>
WHERE y.c2=9;<br>
<br>
&nbsp;c1 | c3 &nbsp;| c2<br>
----+-----+-----<br>
&nbsp;a &nbsp;| yyy | zzz</blockquote><div><br>Your where clause is
filtering out the values.&nbsp; On the second record in X, y.c2 is NULL so
to get 2 rows you would need to write:<br><br>
SELECT x.c1,y.c3,z.c2<br>
FROM x JOIN z USING (c1)<br>
 &nbsp;LEFT OUTER JOIN y ON (x.c2=y.c1)<br>
WHERE y.c2=9 OR y.c2 IS NULL;<br>&nbsp;</div></div><br><br
clear="all"><br>--
<br>==================================================================<br>
Aaron Bono<br> Aranya Software Technologies, Inc.<br> <a
href="http://www.aranya.com">http://www.aranya.com</a><br>
 <a
href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_18927_8737542.1209919577546--