Re: Select an array from one databank and compare it to one from another databank

[Barry Galbraith]

This becomes much easier if you normalize your data, then use JOINs to query your tables.

Consider this.

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `requests` */

DROP TABLE IF EXISTS `requests`;

CREATE TABLE `requests` (
  `request_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `street` varchar(50) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `subject_id` int(10) unsigned DEFAULT NULL,
  `school` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`request_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `requests` */

insert  into `requests`(`request_id`,`street`,`class`,`subject_id`,`school`)
 values (2,'Abbey Road','Performance',6,'Pop');

/*Table structure for table `subjects` */

DROP TABLE IF EXISTS `subjects`;

CREATE TABLE `subjects` (
  `subject_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(50) NOT NULL,
  PRIMARY KEY (`subject_id`,`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `subjects` */

insert  into `subjects`(`subject_id`,`subject`) 
values (1,'lead guitar'),
(2,'rhythm guitar'),
(3,'bass'),
(4,'drums'),
(5,'harmonica'),
(6,'piano');

/*Table structure for table `teach_subj` */

DROP TABLE IF EXISTS `teach_subj`;

CREATE TABLE `teach_subj` (
  `teacher_id` int(10) unsigned NOT NULL,
  `subject_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`teacher_id`,`subject_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `teach_subj_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`teacher_id`),
  CONSTRAINT `teach_subj_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `teach_subj` */

insert  into `teach_subj`(`teacher_id`,`subject_id`)
 values (3,1),
 (1,2),
 (2,3),
 (4,4),
 (1,5),
 (1,6),
 (2,6),
 (4,6);

/*Table structure for table `teachers` */

DROP TABLE IF EXISTS `teachers`;

CREATE TABLE `teachers` (
  `teacher_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `teachers` */

insert  into `teachers`(`teacher_id`,`teacher`)
 values (1,'John'),
 (2,'Paul'),
 (3,'George'),
 (4,'Ringo');


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

mysql> SELECT
    -> r.street
    -> , r.class
    -> , s.subject
    -> , r.school
    -> , t.teacher
    -> FROM
    -> teachers t
    -> JOIN teach_subj ts
    -> ON t.teacher_id = ts.teacher_id
    -> JOIN subjects s
    -> ON ts.subject_id = s.subject_id
    -> JOIN requests r
    -> ON r.subject_id = s.subject_id
    -> WHERE t.teacher = 'Paul';
+------------+-------------+---------+--------+---------+
| street     | class       | subject | school | teacher |
+------------+-------------+---------+--------+---------+
| Abbey Road | Performance | piano   | Pop    | Paul    |
+------------+-------------+---------+--------+---------+
1 row in set (0.00 sec)

Good luck,
Barry.