mysql - 如何编写一对多映射记录的查询

我有这三张桌子。
频道（id，id_account，kind，datetime，state，id_target）
频道联系人（id，id U频道，id U联系人，存档）
频道事件（id，id，频道联系人，代码，详细信息，日期时间）
渠道与渠道的关系

PRIMARY KEY (`id`),
UNIQUE INDEX `CONTRAINT` (`id_channel`, `id_contact`),
INDEX `idx_channel_contact_archived` (`archived`),
INDEX `channel_contacts_ibfk_2` (`id_contact`),
CONSTRAINT `channel_contacts_ibfk_1` FOREIGN KEY (`id_channel`) REFERENCES `channels` (`id`) ON DELETE CASCADE,
CONSTRAINT `channel_contacts_ibfk_2` FOREIGN KEY (`id_contact`) REFERENCES `contact` (`id_contact`) ON DELETE SET NULL

频道事件与频道联系人的关系

PRIMARY KEY (`id`),
INDEX `id_channel_contacts` (`id_channel_contacts`),
CONSTRAINT `channel_events_ibfk_1` FOREIGN KEY (`id_channel_contacts`) REFERENCES `channel_contacts` (`id`) ON DELETE CASCADE

正如人们所看到的，它们是相关的。
我想从channels_events表中得到属于channels表中id_account 123的所有事件。
我写了下面的问题
“on子句”中的未知列“channel_contacts.id_channel”

SELECT channels.id, channels.kind, channels.publish_date,
  channel_contacts.id_channel, channel_contacts.id_contact,
  channel_contacts.archived, channel_events.id,
  channel_events.id_channel_contacts, channel_events.code,
  channel_events.details, channel_events.occation
FROM channel_events
JOIN channels
  ON channels.id = channel_contacts.id_channel
JOIN channel_contacts
  ON channel_contacts.id_contact = channel_events.id_channel_contacts
where channels.id_account = 123;

最佳答案

我认为这是一个输入错误，您已将join with channels表放在channels contacts之前，因此还无法识别channels_contanct。试试这个：

SELECT channels.id, channels.kind, channels.publish_date, channel_contacts.id_channel, channel_contacts.id_contact, channel_contacts.archived, channel_events.id, channel_events.id_channel_contacts, channel_events.code, channel_events.details, channel_events.occation
FROM channel_events
JOIN channel_contacts ON channel_contacts.id= channel_events.id_channel_contacts
JOIN channels ON channels.id = channel_contacts.id_channel
where channels.id_account = 123;