Mysql查询两个表与引用表连接

Question

嗨，我有两个这样的表:

表 1:

name | distro1 | distro2 | distro3
----------------------------------
foo  | 001     | 002     | 003

表 2:

id  | distro 
---------------
001 | slackware
002 | redhat
003 | debian

我想要得到像这样的选择结果=

name | dis1      | dis2   | dis3
----------------------------------
foo  | slackware | redhat | debian

创建这些源表所需的查询。

CREATE TABLE IF NOT EXISTS `table1` (
  `name` varchar(30) NOT NULL,
  `distro1` varchar(30) NOT NULL,
  `distro2` varchar(30) NOT NULL,
  `distro3` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`name`, `distro1`, `distro2`, `distro3`) VALUES
('foo', '001', '002', '003');

CREATE TABLE IF NOT EXISTS `table2` (
  `id` varchar(30) NOT NULL,
  `distro` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table2` (`id`, `distro`) VALUES
('001', 'slackware'),
('002', 'readhat'),
('003', 'debian');

Answer 1

你可以有这样的东西:

通过使用INNeR JOIN，假设所有发行版都有值并且在表2上有相应的匹配

SELECT  a.Name, 
        b.distro Distro1, 
        c.distro Distro2, 
        d.distro Distro3
FROM    myTableA a
            INNER JOIN myTableB b
                on a.distro1 = b.id
            INNER JOIN myTableB c
                on a.distro2 = c.id
            INNER JOIN myTableB d
                on a.distro3 = d.id

更新1

SELECT  a.Name, 
        b.distro Distro1, 
        c.distro Distro2, 
        d.distro Distro3
FROM    myTableA a
            LEFT JOIN myTableB b
                on a.distro1 = b.id
            LEFT JOIN myTableB c
                on a.distro2 = c.id
            LEFT JOIN myTableB d
                on a.distro3 = d.id