Two Table Comparisons

[SupaMonkey]

Something so simple yet I cant get an easy way around this one.

I have two tables:

CREATE TABLE `doctor_tests` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `doctor_id` int(10) unsigned default NULL,
  `test_id` int(10) unsigned default NULL,
  `answer_1` char(1) default NULL,
  `answer_2` char(1) default NULL,
  `answer_3` char(1) default NULL,
  `answer_4` char(1) default NULL,
  `answer_5` char(1) default NULL,
  `answer_6` char(1) default NULL,
  `answer_7` char(1) default NULL,
  `answer_8` char(1) default NULL,
  `answer_9` char(1) default NULL,
  `answer_10` char(1) default NULL,
  `answer_11` char(1) default NULL,
  `answer_12` char(1) default NULL,
  `answer_13` char(1) default NULL,
  `answer_14` char(1) default NULL,
  `answer_15` char(1) default NULL,
  `answer_16` char(1) default NULL,
  `answer_17` char(1) default NULL,
  `answer_18` char(1) default NULL,
  `answer_19` char(1) default NULL,
  `answer_20` char(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tests` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `test` varchar(64) default NULL,
  `released` date default NULL,
  `expires` date default NULL,
  `type` varchar(3) default NULL,
  `question_1` text,
  `answer_1` char(1) default NULL,
  `question_2` text,
  `answer_2` char(1) default NULL,
  `question_3` text,
  `answer_3` char(1) default NULL,
  `question_4` text,
  `answer_4` char(1) default NULL,
  `question_5` text,
  `answer_5` char(1) default NULL,
  `question_6` text,
  `answer_6` char(1) default NULL,
  `question_7` text,
  `answer_7` char(1) default NULL,
  `question_8` text,
  `answer_8` char(1) default NULL,
  `question_9` text,
  `answer_9` char(1) default NULL,
  `question_10` text,
  `answer_10` char(1) default NULL,
  `question_11` text,
  `answer_11` char(1) default NULL,
  `question_12` text,
  `answer_12` char(1) default NULL,
  `question_13` text,
  `answer_13` char(1) default NULL,
  `question_14` text,
  `answer_14` char(1) default NULL,
  `question_15` text,
  `answer_15` char(1) default NULL,
  `question_16` text,
  `answer_16` char(1) default NULL,
  `question_17` text,
  `answer_17` char(1) default NULL,
  `question_18` text,
  `answer_18` char(1) default NULL,
  `question_19` text,
  `answer_19` char(1) default NULL,
  `question_20` text,
  `answer_20` char(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

 

I want to compare doctor_tests to tests to see if a doctor got 80% or more for a test - whats the easiest/cleanest way using mysql or php & mysql? Can it be done with a single SQL statement ?

[fenway]

The reason you're having trouble is that your tables aren't normalized -- see the stickies.

[aschk]

Agreed