Deleting many-to-many realtionships

[dickiedyce]

I have a simple many-to-many three-table set-up:

CREATE TABLE `people` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `salutation` varchar(15) default NULL,
  `forename` varchar(60) NOT NULL default '',
  `middle_initial` varchar(5) NOT NULL default '',
  `surname` varchar(60) NOT NULL default '',
  `suffix` varchar(10) default NULL,
  `is_tenant` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `is_tenant` (`is_tenant`),
  KEY `fullname` (`surname`,`forename`)
)
 
CREATE TABLE `phones` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `phone_label` varchar(127) NOT NULL default '',
  `notes` varchar(127) NOT NULL default '',
  `number` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
)
 
CREATE TABLE `person_phones` (
  `person_id` int(10) unsigned NOT NULL,
  `phone_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`person_id`,`phone_id`),
  KEY `phone_id` (`phone_id`)
)

mapping a person to a number of phones (phones can also be mapped to organisations - another table - which explains why I'm not using a simple one-to-many).

Starting from a value of people.id (which corresponds to person_phones.person_id) I want to be able to delete the link in person_phones and the corresponding entry in phones. I realise that I have to do this in reverse order using some sort of join or sub-select to delete the phones first, and then the link, but for the life of me I can't get the syntax right.

Anyone have any ideas?

[dickiedyce]

Sorry - managed to fix problem myself - was making it over complicated:

delete from phones where id in (select phone_id from person_phones where person_id = 5396);

Again, apologies.

[Atli]

Hi. Welcome to The Scripts!

I'm glad you were able to solve your problem.
Don't hesitate to post again if you have any questions or problems we can help with.