Date: 02/15/06 (MySQL Communtiy)    Keywords: mysql, html, sql

Given the following schema, the following datasets are defined:

A procedure is a collection of testcases, a plan_procedure is a collection of plan_testcases which reflect a temporal snapshot of a procedure collection.

So my question is, given a procedure.id and plan_procedure.id, how do I show the differences between a procedure and a plan_procedure? i.e. show missing testcases from plan_testcases and vice versa? I have been studying the docs on LEFT JOINS and am having problems grokking this one.

Schema follows:

CREATE TABLE `procedures` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `procedure_name` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `procedure_name` (`procedure_name`)
);

CREATE TABLE `testcases` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `procedure_id` int(10) unsigned NOT NULL default '0',
  `procedure_steps` text NOT NULL,
  `expected_results` text NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `plan_procedures` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `procedure_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

CREATE TABLE `plan_testcases` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `plan_procedure_id` int(10) unsigned NOT NULL default '0',
  `testcase_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

Source: http://community.livejournal.com/mysql/85980.html