Having trouble distilling something down to a single query

Disson · #1 (**permalink**) 06-24-04, 12:57

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:

Code:

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.

Code:

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

Code:

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

Code:

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/results

Pat Phelan · #2 (**permalink**) 06-24-04, 13:36

I'd use:

Code:

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

-PatP

Disson · #3 (**permalink**) 06-24-04, 13:44

Sorry, that's how it's in there now, editing above to reflect.

Not the problem.

Pat Phelan · #4 (**permalink**) 06-24-04, 14:00

What version of which database engine are you using?

-PatP

Disson · #5 (**permalink**) 06-24-04, 14:03

Using Sybase 9.

Pat Phelan · #6 (**permalink**) 06-24-04, 14:30

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

Disson · #7 (**permalink**) 06-24-04, 14:49

Code:

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

Code:

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.

Disson · #8 (**permalink**) 06-30-04, 13:17

Give up? ;-)

acg_ray · #9 (**permalink**) 07-07-04, 08:26

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.

rdjabarov · #10 (**permalink**) 07-07-04, 20:35

Quote:

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech