SQL – left joins and where clauses

0 pts.
Tags:
AS/400
I'm writing a query joining multiple tables - I'm doing a left join on the last table that I join because there's data in the other files that I want even if this last table doesn't contain a match. When I add a where clause to the query I don't get results where the last table has a null value. It's like the query changes to an inner join. See below for the query. Is there anyway around this on the iseries? select a.smrn10, c.acctno,d.sma230, d.sma232, c.trancd, c.trncat, e.closcd, c.status, c.batch, c.backdt, e.cashdt from gator.gtsrvdsr as a join gator.gtsrvbal as d on d.blloan=a.smrn10 and d.sma232 <> 0 join conver_bom.aqpcvtno as b on b.rlacct=a.smrn10 join conver_bom.clpacmst as e on e.acctno=b.acctno left join conver_bom.pypsusp as c on c.acctno=b.acctno and c.trancd = '110' where c.backdt <> e.cashdt;
ASKED: October 24, 2005  2:59 PM
UPDATED: October 25, 2005  7:45 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, the part of the criteria placed in WHERE does get treated as an INNER JOIN. Handle this by placing the criteria in the ON clause. Thus, the query should end as follows:

LEFT JOIN conver_bom.pypsusp c ON
c.acctno=b.acctno AND
c.trancd=’110′ AND
c.backdt<>e.cashdt;

Sheldon Linker
Linker Systems, Inc.

http://linkersystems.com

mailto:sol@linker.com
800-315-1174
+1-949-552-1904 from outside of North America

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JPLamontre
    The previous proposition is correct. The original error is that you have not accepted the null value for the e. field : you could have write something like "where c.backdt e.cashdt or e.cashdt is null"
    0 pointsBadges:
    report
  • TheQuigs
    Actually it's your e correlation (conver_bom.pypsusp file) which may return the null so the proper coding would be "where c.backdt is null or c.backdt e.cashdt"
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following