Q:

How to write a query for one to many mapped records

I have got these three tables. 

channels (id, id_account, kind, datetime, state, id_target)
channel_contacts (id, id_channel, id_contact, archived)
channel_events (id, id_channel_contacts, code, details, datetime)

channel_contacts relationship to channels
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

channel_events relationship to channel_contacts
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

As one can see they are related. 
I want all those events from channels_events table which belong to id_account 123 in channels table.
I have written the following query which gives me the following problem of

Unknown column 'channel_contacts.id_channel' in 'on clause'

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;

A:

I think its a typo ,You've placed the join with channels table before channels contacts, so channels_contanct isn't recognized yet. Try this:
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;

