Post reply

Need Help Rewriting Query

  • January 6, 2012 at 10:45 am

    #253007

    I have the following query and it works

    I would like to rewrite this query as an outer join but i donot know how. I wouild like help in rewriting the query.

    Basically, i just want to return records that do not exists in

    F47FirmCommitment but do exists in mf_insured_loan.

    select distinct fk_fha_number,mf_insured_loan.date_firm_commitment

    from mf_insured_loan, F47FirmCommitment

    where (fk_fha_number <> FHACaseNumber)

    and mf_insured_loan.date_firm_commitment is null

    Thanks in advance

  • January 6, 2012 at 10:59 am

    #1429761

    Try this:

    SELECT DISTINCT T1.fk_fha_number

    ,T1.date_firm_commitment

    FROM mf_insured_loan T1

    LEFT JOIN F47FirmCommitment T2

    ON T1.fk_fha_number = T2.FHACaseNumber

    WHERE T1.date_firm_commitment is null

    AND T2.fk_fha_number IS NULL

  • January 6, 2012 at 10:02 pm

    #1429931

    Check out EXCEPT.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 7, 2012 at 4:01 am

    #1429952

    This should work too.

    SELECT mil.date_firm_commitment

    , mil.fk_fha_number

    FROM mf_insured_loan mil

    WHERE NOT EXISTS (

    SELECT *

    FROM F47FirmCommitment FC

    WHERE FC.FHACaseNumber = mil.fk_fha_number

    )

  • January 8, 2012 at 10:21 am

    #1430195

    zwheeler (1/6/2012)

    I have the following query and it works

    I would like to rewrite this query as an outer join but i donot know how. I wouild like help in rewriting the query.

    Basically, i just want to return records that do not exists in

    F47FirmCommitment but do exists in mf_insured_loan.

    select distinct fk_fha_number,mf_insured_loan.date_firm_commitment

    from mf_insured_loan, F47FirmCommitment

    where (fk_fha_number <> FHACaseNumber)

    and mf_insured_loan.date_firm_commitment is null

    Thanks in advance

    Be careful about this. Although the query will produce a result set (a filtered cross join), it certainly won't provide anything resembling your stated requirement - which is what folks will read into it. Better to say "this query executes but the results are not what I'm looking for".

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply