mysql - 将2个表联接成单个查询结果

需要有关查询的帮助。我在数据库中有2个表，联系人和任务。

mysql> describe contacts;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| contact_id | int(11)      | NO   | PRI | NULL    | auto_increment |
| last_name  | varchar(100) | YES  |     | NULL    |                |
| first_name | varchar(100) | YES  |     | NULL    |                |
| email      | varchar(50)  | YES  |     | NULL    |                |
| phone      | varchar(20)  | YES  |     | NULL    |                |
| school_id  | varchar(12)  | NO   |     | NULL    |                |
| access     | char(1)      | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> describe tasks;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| task_id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| task_name         | varchar(125) | YES  |     | NULL    |                |
| task_date         | date         | YES  |     | NULL    |                |
| task_description  | text         | YES  |     | NULL    |                |
| contact_id1       | int(11)      | YES  |     | NULL    |                |
| contact_id2       | int(11)      | YES  |     | NULL    |                |
| contact_id3       | int(11)      | YES  |     | NULL    |                |
| contact_id4       | int(11)      | YES  |     | NULL    |                |
| contact_id5       | int(11)      | YES  |     | NULL    |                |
| contact_id6       | int(11)      | YES  |     | NULL    |                |
| completed         | char(1)      | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

我想从任务表中获取信息，但是我想用匹配的contact_id中的某些字段替换contact_id1-contact_id6。我一直在使用嵌套的选择语句，它可以工作，但是非常混乱。似乎必须有一种更清洁的方法来执行此操作。我以为我有这个

SELECT tasks.task_id, tasks.task_name, tasks.reminder_time, tasks.reminder_interval, CONCAT (contact_1.first_name, " ", contact_1.last_name) as contact_1_name, CONCAT(contact_2.first_name, contact_2.last_name) as contact_2_name, CONCAT(contact_3.first_name, contact_3.last_name) as contact_3_name
FROM tasks
JOIN contacts contact_1 ON tasks.contact_id1 = contact_1.contact_id
JOIN contacts contact_2 ON tasks.contact_id2 = contact_2.contact_id
JOIN contacts contact_3 ON tasks.contact_id3 = contact_3.contact_id

但是我的问题是，它没有显示出contact_id1-contact_id6的任何值都为0的任务，如果未为其设置contact_id，则该值为默认值。

您可以提供的任何帮助都会很棒。

最佳答案

您可以创建一个可以对任务表进行规范化的视图，然后联接将很容易。您可以进行永久查看，也可以仅作选择。

该视图需要以某种方式成为一个联盟

SELECT task_id, task_name, task_description, contact_id1 as contact_id, completed from tasks
UNION ALL
SELECT task_id, task_name, task_description, contact_id2              , completed from tasks
UNION ALL
SELECT task_id, task_name, task_description, contact_id3              , completed from tasks
UNION ALL
SELECT task_id, task_name, task_description, contact_id4              , completed from tasks
UNION ALL
SELECT task_id, task_name, task_description, contact_id5              , completed from tasks
UNION ALL
SELECT task_id, task_name, task_description, contact_id6              , completed from tasks

所以看起来像这样

WITH tasks_easy as (
    SELECT task_id, task_name, task_description, contact_id1 as contact_id, completed from tasks
    UNION
    SELECT task_id, task_name, task_description, contact_id2              , completed from tasks
    UNION
    SELECT task_id, task_name, task_description, contact_id3              , completed from tasks
    UNION
    SELECT task_id, task_name, task_description, contact_id4              , completed from tasks
    UNION
    SELECT task_id, task_name, task_description, contact_id5              , completed from tasks
    UNION
    SELECT task_id, task_name, task_description, contact_id6              , completed from tasks
 )
 select *
 from   tasks_easy t
 join   contacts c   on (t.contact_id = c.contact_id)

还没有测试过，但是应该这样工作。

编辑：实际上正在考虑。 UNION ALL，至少对于临时视图没有必要。如果您的桌子很大，可能会为您带来一些时间上的改善。