mysql查询多表连接

Question

我有以下表格:

machine_machine
id    |     machineid
1     |     EE100034442

item_item
id    |     upc    |    name
2     |    10001   |   Snickers

machine_setup
id    |  machine_id   |    selection    |   item_id
3     |     1         |       A1        |     1

我试图通过连接表来获得以下输出。

machine_setup.machine_id=machine_machine.machineid, machine_setup.selection, item_item.upc, item_item.name

EE100034442  A1  10001  Snickers

表 machine_setup 将由主引用表提供，因为它对每个 machine_id 有多个选择。

Answer 1

根据我目前能看到的唯一 id 来加入，考虑一下:

create table machine_machine
(   id int auto_increment primary key,
    machineid varchar(50) not null
);

create table item_item
(   id int auto_increment primary key,
    upc varchar(30) not null,
    name varchar(100) not null
);

create table machine_setup
(   id int auto_increment primary key,
    machine_id int not null,
    selection varchar(30) not null
);

insert machine_machine(machineid) values ('EE100034442');
insert item_item(upc,name) values ('10001','Snickers');
insert machine_setup(machine_id,selection) values (1,'A1'),(1,'A2'),(1,'A(n)');

select mm.machineid,ms.selection,ii.upc,ii.name 
from machine_setup ms 
join machine_machine mm 
on mm.id=ms.machine_id 
join item_item ii 
on ii.id=ms.machine_id;
+-------------+-----------+-------+----------+
| machineid   | selection | upc   | name     |
+-------------+-----------+-------+----------+
| EE100034442 | A1        | 10001 | Snickers |
| EE100034442 | A2        | 10001 | Snickers |
| EE100034442 | A(n)      | 10001 | Snickers |
+-------------+-----------+-------+----------+