MySQL查询3个表一次

Question

我尝试一次从3个表中获取数据，表是:

候选人
言>
• candidates_language

每一个都表示数据库中所有用户的一些数据。

顾名思义，第一个表包含一些一般数据，如:名，姓，电子邮件。

candidates
        ------------------------------------------------------------------------
        |   id   |     firstname    |      surname       |        email        |
        ------------------------------------------------------------------------
        |   22   |      John        |        Doe         |    [e-mail]     |
        ------------------------------------------------------------------------
        |   23   |      Peter       |       Miller       |    [e-mail]     |
        ------------------------------------------------------------------------

第二个只包含languages_id和languageName

languages
    ---------------------------------
    | languageID |     languageName |
    ---------------------------------
    |      1     |      English     |
    ---------------------------------
    |      2     |      German      |
    ---------------------------------
    |      3     |      Spanish     |
    ---------------------------------

最后一个试着把这些联系在一起:

    candidates_language 
   (foreign keys: candidates_id to table candidates,languageID to languages)
        -----------------------------------------------------------------
        |   id   |   candidates_id  |  languageID |        skill        |
        -----------------------------------------------------------------
        |   1    |        22        |       1     |        basic        |
        -----------------------------------------------------------------
        |   2    |        22        |       3     |        basic        |
        -----------------------------------------------------------------
        |   3    |        23        |       1     |        advance      |
        -----------------------------------------------------------------
        |   4    |        23        |       2     |        basic        |
        -----------------------------------------------------------------

我需要将这3个表的结果连接到一些特定的语言中，并制作如下内容:

---------------------------------------------------------------------------
| id |   firstname  |    surname     |      email      | english | german |
---------------------------------------------------------------------------
| 22 |    John      |      Doe       |  [e-mail]   |  basic  |    -   |
---------------------------------------------------------------------------
| 23 |    Peter     |     Miller     |  [e-mail]   | advance |  basic |
---------------------------------------------------------------------------

我试着用下面的代码连接到这一点::

SELECT
    candidates.firstname,
    candidates.surname,
    candidates_language.candidates_id,
    candidates_language.skill,
    languages.languageID
FROM
    candidates,
    candidates_language,
    languages

不幸的是，我离我想要的实际结果还很远。有人能帮我吗?

Answer 1

如果语言数量是预定义的，那么您可以使用条件聚合来获得所需的结果集:

SELECT c.id, c.firstname, c.surname, c.email,
       MAX(CASE WHEN cl.languageID = 1 THEN cl.skill ELSE '-' END) AS 'English',
       MAX(CASE WHEN cl.languageID = 2 THEN cl.skill ELSE '-' END) AS 'German',
       MAX(CASE WHEN cl.languageID = 3 THEN cl.skill ELSE '-' END) AS 'Spanish'
FROM candidates AS c
LEFT JOIN candidates_language AS cl ON c.id = cl.candidates_id
GROUP BY c.id, c.firstname, c.surname, c.email

演示

否则，您必须使用动态sql。

Answer 2

由于语言不是固定的，所以您不能随意添加select

另一种选择是，对于每种语言，您将获得一条记录。

    select candidates.id, candidates.firstname, candidates.surname, candidates.email, languages.languageName, candidates_language.skill from candidates_language inner join candidates.id on candidates_language.candidates_id inner join languages.id on candidates.languageID

Answer 3

内部连接应该可以。

你可以这样做:

SELECT 
  c.firstname,
  c.surname,
  cl.candidates_id,
  cl.skill,
  l.languageID
FROM candidates c
INNER JOIN candidates_language cl
    on c.id = cl.candidates_id
INNER JOIN languages l
    on l.languageID = cl.languageID