Re: Query Challenge

rdm32_at_dana.ucc.nau.edu (Ryan McLean) wrote
>
> p.s. sample data to illustrate:
>
> TableA:
> id=1234 name=bob
> id=5678 name=mary
> id=9876 name=sue
>
> TableB:
> id=1234 item_type=399000001005
> id=5678 item_type=456123203049
>
> The query will return bob and sue, I need it to return all three.
>

Your problem could be resolved by using ANSI syntax outer join. Like this:

SELECT DISTINCT

       A.LAST_NAME || ', ' || A.FIRST_NAME || ' ' || A.MIDDLE_NAME
         AS FULLNAME
     , DECODE(B.APPLIED_AMT, NULL, '0.00', B.APPLIED_AMT)
         AS APPLIED_AMT
  FROM TableA A
       LEFT OUTER JOIN 
       TableB B 
         ON
             A.ID = B.ID
         AND B.ITEM_TYPE
               IN ('300300001005',
                   '300300002015', '300300003005',
                   '300300004015', '399000001005')