Having trouble distilling something down to a single query

Ok, lets say I have 2 tables, A & B.

Lets say table a has the key A.record_id, and the field emp.

Say table b has the key B.record_id, a foreign key B.a_id that links it to table A, and the field B.date. Now, I want to join these tables as so:

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN "DBA"."B" TABLE B ON
A."RECORD_ID" = B."A_ID"}

You see, I want a list of all A.record_id, whether or not I get a return from the B table.

The problem arises when I want to limit the dates via B.date. It's clear to me what the problem is here, I just don't know a way around it.

WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

So basically, now I'm not getting any a.record_id's for a's that are linked to a b that fall outside of that date range.

Summing up I want...

All A + B where there is a B.date in that range
No A+B for results that are not within the entered date range.
All A's, regardless of if there is a linked B.
All A's, even if there are linked B's outside of the date range.
All in 1 statement (due to environment limitations).

Thanks for your help. I'm pretty much self taught here, so I apologize for not having the language knowledge to make this question more concise. Of course if I knew better how to explain what I'm trying to do then I'd probably know how to do it. ;-)

Mock Sample Data

table A
A001 bill
A002 bill
A003 bill
A004 frank
A005 frank
A006 bob

table B
B001 A001 1/1/2004
B002 A001 1/15/2004
B003 A001 4/1/2004
B004 A003 5/1/2004
B005 A004 1/1/2004
B006 A005 3/3/2004

Mock Results

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A003 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL
A006 bob NULL NULL


edit: added mock data/resultsI'd use:(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))-PatP|||Sorry, that's how it's in there now, editing above to reflect.

Not the problem.|||What version of which database engine are you using?

-PatP|||Using Sybase 9.|||I just noticed some apparent inconsistancies in your query. Could you post the entire query as you are submitting it so that I can try it? I'm using version 8, but I'd expect that to be close enough.

-PatP|||SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" A LEFT OUTER JOIN "DBA"."B" B ON
A."RECORD_ID" = B."A_ID"}
WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

Mock Current Results From Earlier Mock Data

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL

Thanks for looking at this.|||Give up? ;-)|||Try a LEFT OUTER JOIN to a Subquery that restricts your table B.

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN
(Select *
FROM "DBA"."B" TABLE B where ((B.DAte <='1/31/2004' and B.Date >='1/01/2004') OR B.DAte is NULL)) as S1
on A."RECORD_ID" = S1."A_ID"}

I may have a typo with the brackets up there, but something like that should work.

The key is that you are creating a subquery with results restricted to your data range, and then naming that subquery S1. Then the results of S1 are joined to table A.|||1. All A + B where there is a B.date in that range
2. No A+B for results that are not within the entered date range.
3. All A's, regardless of if there is a linked B.
4. All A's, even if there are linked B's outside of the date range.
5. All in 1 statement (due to environment limitations).
Unless I am missing something, - there are contradicting conditions in your requirements:

If #1 is to be met Then #3 & #4 cannot be
If #2 is to be met Then #4 cannot be
If #3 is to be met Then B.date is NULL, thus #1 cannot be
If #4 is to be met...see above

Can you clarify?