Odd Join Scenario

  • I wasn't sure how to search for a solution this scenario. So I'm posting it here hoping for some help. I wouldn't be surprised if it's a simple solution that I'm overlooking. 🙂 


    TableA      TableB     TableC
    ID          ID         ID 
    Name        Address    Description


    TableA       TableB          TableC
    ID  Name     ID  Address     ID  Description
    1   Goofy    1   France      1   Business
    2   Mickey   3   California  2   Vacation home
    3   Donald   5   Florida     5   Home
    4   Minnie
    6   Daisy

    This is what I need the result set to look like:

    ID   Name       Address     Description
    1    Goofy      France      Business
    2    Mickey                 Vacation home
    3    Donald     California
    4    Minnie
    5               Florida      Home
    6    Daisy

    Any ideas how to write the query?  Thanks in advance! 🙂

  • And the answer is FULL OUTER JOIN! Duh! lol *sigh* I need more caffeine.

  • Use this.

    select coalesce(a.id,b.id,c.id)id,
    isnull(max(name),'')name,
    isnull(max(address),'')address,
    isnull(max(descri),'')descr from tablea a full join tableb b
    on a.id=b.id
    full join tablec c
    on a.id=c.id
    group by coalesce(a.id,b.id,c.id)

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

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