MySQL从两列连接两个表

I'm going to join two table with two columns contain id from another table.

我要加入两个表,其中两列包含来自另一个表的id。

Table users

id | first_name | last_name |
---+------------+-----------+
 1 | John       | Doe       |
 2 | Jane       | Doe       |
 3 | Some       | Name      |

Table stamp

id |    date    | applicant_id  | app_by_id |
---+------------+---------------+-----------+
 1 | 2013-03-15 | 1             | 2         |
 2 | 2013-03-10 | 2             | 3         |
 3 | 2013-03-13 | 2             | 1         |

What I want to show:

我要展示的内容:

    date    | applicant | app_by    |
------------+-----------+-----------+
 2013-03-15 | John Doe  | Jane Doe  |
 2013-03-10 | Jane Doe  | Some Name |
 2013-03-13 | Jane Doe  | John Doe  |

My query:

SELECT CONCAT_WS(' ', NULLIF(t1.first_name, ' '), NULLIF(t1.last_name, ' ')) AS applicant,
    CONCAT_WS(' ', NULLIF(t1.first_name, ' '), NULLIF(t1.last_name, ' ')) AS app_by,
    t2.date
    FROM users t1
    INNER JOIN stamp t2 ON applicant_id = t1.id

I know there's something wrong with my query, but I don't know how to fix it.

我知道我的查询有问题,但我不知道如何修复它。

3 个解决方案

#1

You probably want to use LEFT JOIN so that you get values in each column even if the other join conditions fail. The following code also works with regular JOIN.

您可能希望使用LEFT JOIN,以便即使其他连接条件失败,也可以在每列中获取值。以下代码也适用于常规JOIN。

SELECT s.date,
CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by
FROM stamp s
LEFT JOIN users u1 ON s.applicant_id = u1.id
LEFT JOIN users u2 ON s.app_by_id = u2.id

Proof: http://sqlfiddle.com/#!2/39aff/1/0

#2

Try your joins like

试试你的联接

SELECT  CONCAT_WS(' ', NULLIF(u1.first_name, ' '), NULLIF(u1.last_name, ' ')) AS applicant,
        CONCAT_WS(' ', NULLIF(u2.first_name, ' '), NULLIF(u2.last_name, ' ')) AS app_by,
        s.date
FROM    stamp s INNER JOIN
        user u1 ON  s.applicant_id = u1.id INNER JOIN
        user u2 ON  s.app_by_id = u1.id

#3

Instead of joins, try to use sub query like this.

而不是连接,尝试使用这样的子查询。

SELECT date, 
(SELECT CONCAT_WS(' ', NULLIF(first_name, ' '), NULLIF.last_name, ' ')) from USERS WHERE id = s.applicant_id) as applicant, 
(SELECT CONCAT_WS(' ', NULLIF(first_name, ' '), NULLIF.last_name, ' ')) from USERS WHERE id = s.app_by_id) as app_by 
from stamp s

#1