Post reply

Priority based selection from SQL JOIN

  • September 9, 2011 at 7:15 pm

    #390378

    Hi,

    I have three tables(T1,T2 & T3) with same attributes but different values. I have to select all the fields that are not NULL from T3. If there is any attribute with NULL, then I should go to T2 to get its value, If even T2 has NULL, I should go to the T1 for the same (Priority: T3>T2>T1). Can anyone help me how to do this?

    Thanks

  • September 10, 2011 at 2:07 am

    #1379571

    The answer is Zero. 😀

    First part:

    I have to select all the fields that are not NULL from T3.

    Except the fact that we don't really have "fields" (that's more a term used in agriculture or Excel), this will by definition eliminate the second part:

    If there is any attribute with NULL

    .

    If I misunderstood your requirement, please be more descriptive. The best way would be to provide some ready to use test data as described in the first link in my signature.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 10, 2011 at 8:59 am

    #1379602

    You give no indication how to determine which row in T2 corresponds to a row in T3, or which row in T1 corresponds to a row in T2. Do they share a primary key of some sort? Or what?

    If you have a ID field dommon to all three tables which uniquely identifies a row, and more fields called Fa, Bb, Fc you can write something like

    select id,

    coalesce(T3.Fa,T2.Fa,T2.Fa) as Fa,

    coalesce(T3.Fb,T2.Fb,T2.Fb) as Fb,

    coalesce(T3.Fc,T2.Fc,T2.Fc) as Fc,

    from T3 left join T2 on T3.id = T2.id left join T1 on T2.id = T1.id

    obviously iif the primary key has multiple fileds all those fields have to be specified in the first line and used in the two join conditions. Similarly if there are more non-key fields they would be be added as extra lines to the list of outputs defined using coalesce.

    Tom

Viewing 3 posts - 1 through 3 (of 3 total)

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