MySQL：如何在多对多关系中创建两个表之间的映射

I have two relevant tables:

我有两个相关的表:

Trainee and Device

实习生和设备

Each trainee needs to do a training with the device, in order to be allowed to use it. As a trainee needs to be trained in multiple devices and one single device can be used by multiple users I have created a many-to-many relationship between those two.

每个受训者都需要对设备进行培训,以便允许使用它。由于受训者需要在多个设备上接受培训,并且多个用户可以使用一个设备,因此我在这两个设备之间创建了多对多关系。

MySQLWorkbench therefore created me another table which is called 'trainee_has_device' and holds a foreign key to trainee.Id and a foreign key to device.Id.

MySQLWorkbench因此创建了另一个名为“trainee_has_device”的表,并为trainee.Id和device.Id的外键保存了一个外键。

So far, so good.

到现在为止还挺好。

I need to get the training status for a trainee to a specific device, so I added a property TrainingStatus on 'trainee_has_device'. My tables then look like this:

我需要将受训者的培训状态提供给特定设备,因此我在'trainee_has_device'上添加了一个属性TrainingStatus。我的表格看起来像这样:

|trainee|             
--------------------- 
id 
surname

---

|device|
---------------------
id
deviceName

---

|trainee_has_device|
---------------------
traineeId
deviceId
TrainingStatus

I need to get the training value of every person to every device even if there is no entry in 'trainee_has_device' (if so I would like to get null)

即使“trainee_has_device”中没有条目,我也需要将每个人的训练值都提供给每个设备(如果是这样我想得到null)

My query looked like this:

我的查询看起来像这样:

SELECT * FROM trainee LEFT OUTER JOIN trainee_has_device ON trainee_has_device.traineeId = trainee.id LEFT OUTER JOIN device ON device.id = trainee_has_device.deviceId

But Unfortunately I did only get entries which were listed in trainee_has_device or null.

但不幸的是,我只获得了在trainee_has_device中列出的条目或null。

How can I get the TrainingStatus of every person to every device even if there is no entry in 'trainee_has_device'?

即使“trainee_has_device”中没有条目,我怎样才能将每个人的TrainingStatus都提供给每个设备?

2 个解决方案

#1

I need to get the training value of every person to every device even if there is no entry in 'trainee_has_device'

即使“trainee_has_device”中没有条目,我也需要将每个人的培训价值都提供给每个设备

This doesn't make sense, the trainee_has_device table has to be filled first or it won't show you any data. In real world, all of the trainees has to be assigned a set of devices with a default status (New,Familiar...) for each assignment. So if the connection isn't available between Trainee A and Device C it just means that Device C isn't assigned to Trainee A

这没有意义,必须首先填充trainee_has_device表,否则它不会显示任何数据。在现实世界中,必须为所有受训者分配一组具有默认状态(新的,熟悉的......)的设备。因此,如果在受训者A和设备C之间没有连接,则仅表示设备C未分配给受训者A.

#2

I managed to get it working with a subquery:

我设法使用子查询:

SELECT trainee.id as TraineeId, trainee.Prename, trainee.Surname, trainee.Mail, trainee.Department,
trainee.Telephone, device.Name as DeviceName, (SELECT IF (trainee_has_device.trainee_id = trainee.id AND
trainee_has_device.device_id = device.id, trainee_has_device.TrainingStatus, 'N/A')
FROM trainee_has_device WHERE trainee_has_device.trainee_id = trainee.id AND device.id = trainee_has_device.device_id) AS TrainingStatus
FROM trainee JOIN device ORDER BY trainee.Surname, trainee.id, device.Name ASC