SQL query return records even when criteria in one table is not satisfied

jeffbarbacpa
jeffbarbacpa used Ask the Experts™
on
I have two tables.  

LN_CODES which has the loan number LNKEY
LN_FEES which is a list of loan fees FEENAME, BORRPART

I want to show the loan number, and the flood fee.  If there is no flood fee I still want to show the loan number, and show either $0 or null as the flood fee.  Here is my current query.  Any help would be appreciated!

SELECT     EMPOWER.U_LN_CODES.LNKEY, EMPOWER.LN_FEES.FEENAME, EMPOWER.LN_FEES.BORRPART
FROM         EMPOWER.U_LN_CODES LEFT OUTER JOIN
                      EMPOWER.LN_FEES ON EMPOWER.U_LN_CODES.LNKEY = EMPOWER.LN_FEES.LNKEY
WHERE     (EMPOWER.U_LN_CODES.LNKEY = '2011-1784433') AND (EMPOWER.LN_FEES.FEENAME = 'Flood')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The FEENAME should be in your join criteria not the where clause.  By putting it in the where clause you are making the join and inner join.  Like this.

SELECT     EMPOWER.U_LN_CODES.LNKEY, EMPOWER.LN_FEES.FEENAME, EMPOWER.LN_FEES.BORRPART
FROM         EMPOWER.U_LN_CODES LEFT OUTER JOIN
                      EMPOWER.LN_FEES ON EMPOWER.U_LN_CODES.LNKEY = EMPOWER.LN_FEES.LNKEY
                      AND EMPOWER.LN_FEES.FEENAME = 'Flood'
WHERE     (EMPOWER.U_LN_CODES.LNKEY = '2011-1784433')

Greg

Author

Commented:
perfect...thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial