代码之家 › 专栏 › 技术社区 › DrakeMurdoch

如何将两个已联接的子查询联接为总共五个表联接

join sql-server sql

DrakeMurdoch · 技术社区 · 7 年前

我有两个查询,每一个都能很好地工作,并给出我想要的结果。

问题1:

SELECT COALESCE(m.ID, r.ID, s.ID) ID, 
    COALESCE(m.Test, r.Test) Test, 
    COALESCE(m.School, r.School, s.School) School, 
        m.M_Col1, m.M_Col2, m.M_Col3,  
        r.E_Col1, r.E_Col2, r.E_Col3, 
        s.First, s.Last 
    FROM Tabl1 AS m 
FULL OUTER JOIN Table2 AS r 
    ON(m.Test = r.Test AND m.ID = r.ID) 
INNER JOIN Table3 AS s 
    ON COALESCE(m.ID, r.ID)= s.ID

这给了我:

ID   TEST       M_Col1  M_Col2  M_Col3  E_Col1  E_Col2  E_Col3  First  Last
101  2013-MEG3   Stuff   Stuff   Stuff   Stuff   Stuff   Stuff   John  Smith
102  2013-MG3    Stuff   Stuff   Stuff                           Jane  Doe
102  2013-EG3                            Stuff   Stuff   Stuff   Jane  Doe
103  2013-MG5    Stuff   Stuff   Stuff                           Joe   Smithe
103  2013-EG5                            Stuff   Stuff   Stuff   Joe   Smithe
104  2013-MEG6   Stuff   Stuff   Stuff   Stuff   Stuff   Stuff   Jane  Dawn
105  2013-MG6    Stuff   Stuff   Stuff                           Jaime Swans
106  2013-EG6                            Stuff   Stuff   Stuff   Jaime Swans

我的第二个问题,问题2:

SELECT a.ID, a.Test, t.Other, t.Other_No 
   FROM Table4 as t
FULL OUTER JOIN Table5 as a
   ON (a.Other_Key = t.Other_Key)

这给了我一个类似的表结果。

我要做的是将这两个子查询连接在一起,基本上:

(
  SELECT
    COALESCE(m.ID, r.ID, a.ID) ID, 
    COALESCE(m.Test, r.Test, a.Test) Test, 
    COALESCE(m.School, r.School, s.School) School, 
        m.M_Col1, m.M_Col2, m.M_Col3,  
        r.E_Col1, r.E_Col2, r.E_Col3, 
        s.First, s.Last 
FROM
  Tabl1 AS m 
FULL OUTER JOIN
  Table2 AS r 
    ON(m.Test = r.Test AND m.ID = r.ID) 
INNER JOIN
  Table3 AS s 
    ON COALESCE(m.ID, r.ID)= s.ID
)
  q1

FULL OUTER JOIN

(
  SELECT
    a.ID, a.Test, t.Other, t.Other_No 
  FROM
    Table4 as t
  FULL OUTER JOIN
    Table5 as a
      ON (a.Other_Key = t.Other_Key)
)
  q2
    ON(q1.ID = q2.ID)

但这不管用。它只给了我查询1的结果。我做错什么了?

0 回复 | 直到 7 年前

Thermos 7 年前

也许在这里假设很多,但我认为这可能接近你想要的:

SELECT COALESCE(m.ID, r.ID, s.ID, q2.ID) ID, --added ID from query 2 to coalesce function
    COALESCE(m.Test, r.Test, s.Test, q2.Test) Test, --added Test from query 2 to coalesce function
    COALESCE(m.School, r.School, s.School) School, 
    m.M_Col1, m.M_Col2, m.M_Col3,  
    r.E_Col1, r.E_Col2, r.E_Col3, 
    s.First, 
    s.Last 
    --add q2.Other or q2.Other_No here if you want to return from query 2
FROM Tabl1 AS m 
FULL OUTER JOIN Table2 AS r 
    ON(m.Test = r.Test AND m.ID = r.ID) 
INNER JOIN Table3 AS s 
    ON COALESCE(m.ID, r.ID)= s.ID
FULL OUTER JOIN (
    SELECT b.ID, b.Test, t.Other, t.Other_No 
    FROM Table4 as t
    FULL OUTER JOIN Table5 as a
    ON (a.Other_Key = t.Other_Key)
) q2 ON COALESCE(m.ID, r.ID, s.ID) = q2.ID