none
how to outer join 3 tables

    Question

  • Hi folks,
    I've been avoiding ANSI joins for awhile, and am finally doing it because of this scenario -- gap checking.

    I need to outer-join 3 tables, A, B, C using shared column X.   The problem is to find all gaps in the 1:1:1 (???) relationships between them by listing all relationships or lack thereof.   This is an oversimplification of the actual problem (which attempts several columns), but at least maybe I can get the structure and syntax right.  I haven't yet found good examples online.

    create table A (x int null, y int null)
    create table B (x int null, y int null)
    create table C (x int null, y int null)
    go

    insert A (x,y) values (1, 91)
    insert A (x,y) values (2, 92)
    insert A (x,y) values (3, 93)
    insert B (x,y) values (1, 94)
    insert B (x,y) values (2, 95)
    insert C (x,y) values (1, 96)
    go

    SELECT A.y, B.y, C.y
    FROM
    A
    FULL OUTER JOIN B ON B.x = A.x OR B.x = C.x
    FULL OUTER JOIN C ON C.x = A.x OR C.x = B.x


    This does not work:
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "C.x" could not be bound.

    It's a little circular, but I'm surprised sql server couldn't resolve it, unless my syntax is way off. 

    I'm sure this is a multi-parter.  I *am* RTFM-ing in the meantime, but thanks for any quick help.

    Monday, November 10, 2008 11:39 PM

Answers

  • You have to join them in order. You can't join on a FK that hasn't be joined yet:

     

    So...

     

    SELECT A.y, B.y, C.y
    FROM
    A
    FULL OUTER JOIN B ON B.x = A.x
    FULL OUTER JOIN C ON C.x = A.x OR C.x = B.x

     

    A.D.T.

     

    Monday, November 10, 2008 11:42 PM

All replies

  • You have to join them in order. You can't join on a FK that hasn't be joined yet:

     

    So...

     

    SELECT A.y, B.y, C.y
    FROM
    A
    FULL OUTER JOIN B ON B.x = A.x
    FULL OUTER JOIN C ON C.x = A.x OR C.x = B.x

     

    A.D.T.

     

    Monday, November 10, 2008 11:42 PM
  • Interesting, ok. 

    So what happens when there is no A record, but C+B are related?  It seems like this lets you indicate the joins (cardinality) of B-to-A and C-to-A but not C-to-B (or vice versa).
    Monday, November 10, 2008 11:54 PM
  • A FULL OUTER JOIN will give you ALL records from both tables whether there is a match on the FK or not. So it doesn't matter.

     

    So your OR clause is irrelevant.

     

    A.D.T.

    Tuesday, November 11, 2008 4:03 AM
  • It's called the transitive property of inequality. If A = B and B = C then A = C.

     

    Because you're using a FULL OUTER JOIN, you'll get the relationship you're looking for.

     

    A.D.T.

     

    Tuesday, November 11, 2008 5:33 PM
  • I don't understand how the optimizer can infer the exact relationship of B and C because the joins are explicity with respect to A (all cardinality), even if an outer join.  Maybe it can in this case because they share key X.  What if they all related differently?

    SELECT A.*, B.*, C.*
    FROM A
    FULL OUTER JOIN B ON B.x = A.x
    FULL OUTER JOIN C ON C.y = A.y OR C.z = B.z


    Does this property still hold because of the relationship of the rows, and not the specific equality?   (I'm curious why you referred to it as "transitive propert of inequality")

    (Transitive property -- that's one of those concepts they teach as rote vocabulary in school and is quickly forgotten .)

    Tuesday, November 11, 2008 7:58 PM
  • ...which is why you should use an inner join to find relationships. The query optimizer isn't infering anything.

     

    Logically, you are concluding equality by proving false inequality.

     

    You must decide on your desired results. Can  you acheive it in a single query?

     

    A.D.T.

    Tuesday, November 11, 2008 8:11 PM
  • Well, for the specific problem I want to identify all rows and their relationships or lack of relationships.  A full outer join should reveal both the relationships and the lack of relationships at once, distinguishable by nulls.  That's just considering a 1:1:1 relationship  (or rather, a 0..1:0..1:0..1 relationship).

    This appears to have done just that.

    What I don't understand is the semantics behind this and what the syntax actually denotes.   I'm also not seeing a fundamental advantage of the ANSI syntax itself, except for allowing specific query types which the previous syntax could have represented more simply (eg: A.x *=* B.x).


    Tuesday, November 11, 2008 9:24 PM
  • ;with Xs as ( select x from a union select x from b union select x from c )select Xs.*,a.y,b.y,c.y from Xs left join a on a.x = Xs.x left join b on b.x = Xs.x left join c on c.x = Xs.x



    • Edited by Vernard Wednesday, September 18, 2013 1:27 AM used reservered word for variable
    Wednesday, September 18, 2013 1:19 AM