Query Challenge

Hello everyone I've got a query problem that is really kicking my butt and I am hoping that someone will be able to help me out.

Here is the scenario:

I have 2 tables: tableA and tableB
They both have the fields id, semester. TableA has the name that corresponds to the id. Tableb has the amount and item_type that corresponds to the id. The semester and item_type will restrict the query.

The result that I would like to accomplish is getting all names in TableA wether they are in TableB or not. BUT, if they are in TableB the item_type must be in a predetermined list. The id could be in TableB but not having the item_type I am looking for. Now this is really screwing me up, maybe what I have so far will clear things up . . .

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,
TableB B
WHERE A.ID = B.ID(+)
AND A.SEMESTER = '1037'
AND (B.ITEM_TYPE IS NULL OR B.ITEM_TYPE IN ('300300001005',
'300300002015', '300300003005',
'300300004015', '399000001005'));

If I take out the last lines -->AND (B.ITEM_TYPE IS NULL OR B.ITEM_TYPE IN ('300300001005', '300300002015', '300300003005',
'300300004015', '399000001005'))<-- I get the results that I want, for
the results from TableB may or may not be in the desired item_type. In other words the amount is not valid (to me) unless it is in the listed item_types.

I hope this makes sense.

Thank you in advance. I really appreciate any assistance.

Ryan McLean

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.

Thank you! Received on Wed May 21 2003 - 00:18:12 CEST