Re: SQL question
I must say, those are pretty intuitive column and table names. This is an outer join which should return all rows from the first table where 'whatever'='PR' and any joining rows which may exist in the second table - one catch to this is that you are specifically asking for NULL=NULL and NULL is never "equal to" or "unequal to". That join should properly be "caa443400014 IS NULL AND caa440400014 IS NULL"<br /><br />j.<br /><br /><p>On Jun 5, 2009, <strong>Kennedy, Randy</strong> <RKennedy@scottsdaleaz.gov> wrote: </p><div class="replyBody"><blockquote style="padding-left: 1ex; margin: 0pt 0pt 0pt 1.8ex; border-left: #267fdb 2px solid">IDS 9.40.FC3 on HPUX 11.11<br /><br />When I run the following SQL (Informix Join), I get back almost every<br />record and they don't meet the criteria; however, if I run the statement<br />using SQL join, I get the correct results. Any idea of why the<br />difference or did I create it incorrectly?<br /><br />select caa443400013,caa443400014<br />from caa44340,outer caa44040<br />where caa443400011 = caa440400011<br />and caa443400012 = caa440400012<br />and caa443400013 = caa440400013<br />and caa443400014 = caa440400014<br />and caa440400014 is null<br />and caa443400013 = 'PR'<br /><br /><br /><br />SELECT caa443400013, caa443400014<br />FROM caa44340 LEFT JOIN caa44040 ON (caa443400011 = caa440400011) AND<br />(caa443400012 = caa440400012) AND (caa443400013 = caa440400013) AND<br />(caa443400014 = caa440400014)<br />WHERE (((caa440400014) Is Null) AND ((caa443400013)='PR'));<br /><br /><br /><br />Thanks,<br />Randy<br />_______________________________________________<br />Informix-list mailing list<br /><a href="mailto:Informix-list@iiug.org" target="_blank" class="parsedEmail">Informix-list@iiug.org</a><br /><a href="http://www.iiug.org/mailman/listinfo/informix-list"; target="_blank" class="parsedLink">http://www.iiug.org/mailman/listinfo/informix-list</a><br /></blockquote></div>