From: Daevid Vincent
Date: August 13 2008 8:51pm
Subject: Help with query to remove all records where foreign key doesn't have corresponding records
List-Archive: http://lists.mysql.com/mysql/214092
Message-Id: <64d401c8fd86$5e043c40$1a0cb4c0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I want to remove all records from 'feed_tag' where the feed_id foreign =
key
doesn't have any corresponding records in feed.=20

For instance I may have a record in feed_tag that is like (23, 10, 4543,
'... (some date)').=20

Then lets say there is no record in feed that has a primary id key of =
10.=20

I want that record (or usually records because of the 1 feed to many
feed_tag relationship) to be removed.

CREATE TABLE IF NOT EXISTS `feed` (
=A0 `id` int(11) NOT NULL auto_increment,
=A0 `title` varchar(100) default NULL,
=A0 `url` varchar(255) default NULL,
=A0 `host` varchar(100) default NULL,
=A0 `type` varchar(100) default NULL,
=A0 `status` char(1) default NULL,
=A0 `total_stories` int(11) default '0',
=A0 `created_at` datetime default NULL,
=A0 `updated_at` datetime default NULL,
=A0 PRIMARY KEY=A0 (`id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8 ;

CREATE TABLE IF NOT EXISTS `feed_tag` (
=A0 `id` int(11) NOT NULL auto_increment,
=A0 `feed_id` int(11) default NULL,
=A0 `tag_id` int(11) default NULL,
=A0 `created_at` datetime default NULL,
=A0 PRIMARY KEY=A0 (`id`),
=A0 KEY `feed_tag_FI_1` (`feed_id`),
=A0 KEY `feed_tag_FI_2` (`tag_id`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8 ;

As you can see the foreign key 'feed_id' is the issue here (ignore the
tag_id key).=20