Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: 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:

    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
    Last edited by Disson; 06-24-04 at 14:45.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    (B."DATE" IS NULL OR
        (B."DATE" >= {d '2004-01-01'} AND
        B."DATE" <= {d '2004-01-31'}))
    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Sorry, that's how it's in there now, editing above to reflect.

    Not the problem.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What version of which database engine are you using?

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    15
    Using Sybase 9.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Mar 2004
    Posts
    15
    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.

  8. #8
    Join Date
    Mar 2004
    Posts
    15
    Give up? ;-)

  9. #9
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Try LEFT OUTER JOIN to Subquery

    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.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •