MySQL 将两个结果连接在一起？

Question

我试图在一个 View /表中将两个查询结果连接在一起。我试过这段代码很接近，但结果合并在一起，如下所示。我需要它们是单独的列。我尝试过联接和联合，但似乎没有任何效果。

输出:

SQL查询:

`SELECT `PARTY DESCRIPTION` 
FROM `PARTY` 
WHERE
  `PARTY CODE` IN (
    SELECT `PARTY CODE` 
    FROM `PARTY CANDIDATE` 
    WHERE `CANDIDATE ID` IN (
        SELECT `CANDIDATE ID` 
        FROM OFFICE 
        WHERE `OFFICE DESCRIPTION` = 'MAYOR' 
      ) 
    ) 
  UNION
  (
    SELECT `CANDIDATE NAME` 
    FROM `CANDIDATE` 
    WHERE `CANDIDATE ID` IN (
        SELECT `CANDIDATE ID` 
        FROM OFFICE 
        WHERE `OFFICE DESCRIPTION` = 'MAYOR' 
      ) 
    );`

行:5

PARTY DESCRIPTION
Republican
Democrat
Giuliani
Dinkins
Ferucci

我想要的:

PARTY DESCRIPTION    NAMES
Republican          Giuliani
Democrat            Ferucci
                    Dinkins

架构:

Answer 1

如果我对这个问题的理解正确，那么您正在尝试JOIN 两个查询，以便显示担任市长职务的候选人的成对候选人姓名及其所属政党。

你可以尝试这样的事情:

SELECT `PARTY DESCRIPTION`, `CANDIDATE NAME`
FROM   `PARTY` p
JOIN   `PARTY CANDIDATE` pc ON pc.`PARTY CODE` = p.`PARTY CODE`
JOIN   `CANDIDATE` c ON pc.`CANDIDATE ID` = c.`CANDIDATE ID`
JOIN   `OFFICE` o ON pc.`CANDIDATE ID` = o.`CANDIDATE ID`
WHERE  `OFFICE DESCRIPTION` = 'MAYOR`