SQL Server Performance

join query

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Nov 28, 2007.

  1. arkiboys Member

    Hi,
    There are two table variables with the following fields:
    @tblBestBidsForToday
    Security_ID source_code Best_Bid Import_Date
    @tblBestAsksForToday
    Security_ID source_code Best_Ask Import_Date
    Now I have the following query to get the data from both tables.
    select
    b.Security_ID,
    b.Source_Code,
    b.Best_Bid,
    a.Best_Ask,
    b.Import_Date
    from
    @tblBestBidsForToday b
    left join @tblBestAsksForToday a on b.Security_ID = a.Security_ID
    and b.Source_Code = a.Source_Code
    and b.Import_Date = a.Import_Date
    I would like to get all the data from both tables.
    But it seems that this query does not fully work.
    Because there may be records in the first table and not in the second or vice versa.
    There may be a source_Code with an import_Date in the first table, this source_code can be present in the second table but not with all the same Import_Dates.
    @tblBestBidsForToday
    Security_ID source_code Best_Bid Import_Date
    125 TR73 112.6250 2007-11-28 07:23:00
    125 TTFN03 112.8750 2007-11-28 07:25:00
    125 TTFN03 112.8750 2007-11-28 07:27:00
    125 TTFN03 112.8750 2007-11-28 07:29:00
    125 GFI01 112.9370 2007-11-28 07:31:00
    125 GFI01 112.9370 2007-11-28 07:33:00
    ...
    ...
    @tblBestAsksForToday
    Security_ID source_code Best_Bid Import_Date
    125 GFI01 113.1250 2007-11-28 07:29:00
    125 GFI01 113.0620 2007-11-28 07:31:00
    125 GFI01 113.0620 2007-11-28 07:33:00
    125 GFI01 113.0620 2007-11-28 07:35:00
    125 GFI01 113.0620 2007-11-28 07:39:00
    125 GFI01 113.0620 2007-11-28 07:41:00
    ...
    ...

    CURRENT RESULT (which is not quite correct):
    Note, in @tblBestAsksForToday, this record does not appear in the RESULT
    125 GFI01 113.1250 2007-11-28 07:29:00

    RESULT
    125 TR73 112.6250 NULL 2007-11-28 07:23:00
    125 TTFN03 112.8750 NULL 2007-11-28 07:25:00
    125 TTFN03 112.8750 NULL 2007-11-28 07:27:00
    125 TTFN03 112.8750 NULL 2007-11-28 07:29:00
    125 GFI01 112.9370 113.0620 2007-11-28 07:31:00
    125 GFI01 112.9370 113.0620 2007-11-28 07:33:00
    arkiboys, Nov 28, 2007
    #2

  2. FrankKalis Moderator

    Seems like you should not use a LEFT JOIN but rather a FULL OUTER JOIN instead.
    FrankKalis, Nov 28, 2007
    #3

  3. arkiboys Member

    Tried that but the very same record I mentioned is still missing.
    arkiboys, Nov 28, 2007
    #4

  4. FrankKalis Moderator

    Do you still use this JOIN condition?
    left join @tblBestAsksForToday a on b.Security_ID = a.Security_ID
    and b.Source_Code = a.Source_Code
    and b.Import_Date = a.Import_Date
    If so, it is understandable that this row isn't contained in the final resultset Can you omit the Import_Date condition?
    left join @tblBestAsksForToday a on b.Security_ID = a.Security_ID
    and b.Source_Code = a.Source_Code
    FrankKalis, Nov 28, 2007
    #5

Share This Page