php – 在MySql中选择与一对多reationship右侧匹配的行

产品,主管和地址表与公司表有一对多的关系.

因此,一家公司可以拥有许多产品,许多地址和许多董事.

CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,`company_name` varchar(50) NOT NULL,PRIMARY KEY (`company_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,`company_id` int(11) NOT NULL,`product` varchar(50) NOT NULL,PRIMARY KEY (`product_id`),KEY `company_id` (`company_id`),KEY `product` (`product`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `directors` (
  `director_id` int(11) NOT NULL AUTO_INCREMENT,`surname` varchar(100) NOT NULL,`dob` date NOT NULL,PRIMARY KEY (`director_id`),KEY `surname` (`surname`),KEY `dob` (`dob`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `addresses` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,`company_id` int(1) NOT NULL,`postcode` varchar(10) NOT NULL,PRIMARY KEY (`address_id`),KEY `postcode` (`postcode`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `companies` (`company_id`,`company_name`) VALUES
(1,'Honda'),(2,'Toyota');

INSERT INTO `products` (`product_id`,`company_id`,`product`) VALUES
(1,1,'Civic'),'Accord'),(3,2,'Corolla'),(4,'Prius'),(5,'CRV');

INSERT INTO `directors` (`director_id`,`surname`,`dob`)     VALUES
(1,'Jones','1990-09-09'),'Smith','1980-08-08'),'Lucas','1970-07-07'),'Kelly','1960-06-06'),'Monty','1950-05-05');

INSERT INTO `addresses` (`address_id`,`postcode`) VALUES
(6,'12345'),(7,'23456'),(8,'34567'),(9,'45678'),(10,'56789');

我试图编写一个有效的查询(使用MySql / PDO)来找到匹配匹配导演(姓氏和dob)和地址(邮政编码)的公司的产品.

我只想列出每行一个匹配的产品,而不是单独列出每个导演或邮政编码.

到目前为止,我有下面的查询,这似乎有效,但它很难看,我怀疑在速度和效率方面这是一个荒谬的方式.

SELECT product
FROM products p
LEFT JOIN companies c USING(company_id)
WHERE :lname IN ( 
    SELECT surname 
    FROM directors d 
    WHERE c.company_id = d.company_id )
AND :dob IN ( 
    SELECT dob 
    FROM directors d 
    WHERE c.company_id = d.company_id )
AND :postcode IN ( 
    SELECT postcode 
    FROM addresses a 
    WHERE c.company_id = a.company_id )

预先感谢您的帮助.