Как эффективно запрашивать отношения «многие ко многим» на двух столбцах

Я создал эти три таблицы, которые представляют собой отношения «многие ко многим» между врачом и специальностями врача.

У врача может быть много специальностей, и у многих врачей может быть определенная специальность.

CREATE TABLE doctors ( doctor_id int(11) NOT NULL AUTO_INCREMENT, firstname varchar(30) NOT NULL, lastname varchar(30) NOT NULL, area varchar(50) NOT NULL, PRIMARY KEY (doctor_id) ); CREATE TABLE has_specialty ( doctor_id int(11) NOT NULL, specialty_id int(11) NOT NULL, PRIMARY KEY (doctor_id,specialty_id), FOREIGN KEY (doctor_id) REFERENCES doctors (doctor_id), FOREIGN KEY (specialty_id) REFERENCES specialties (specialty_id) ); CREATE TABLE IF specialties ( specialty_id int(11) NOT NULL AUTO_INCREMENT, specialty varchar(254) NOT NULL UNIQUE, PRIMARY KEY (specialty_id) ); 
  • Я хочу найти всех врачей, которые имеют специальность «H» и находятся в области «B».

Так например, скажем, у нас есть эта база данных:

 DOCTORS +-----------+-----------+----------+----------+ | doctor_id | firstname | lastname | area | +-----------+-----------+----------+----------+ | 1 | Virginia | Clark | A | | 2 | Jane | Brown | B | | 3 | Adam | Harris | D | | 4 | Anthony | Rogers | D | | 5 | Paula | Lopez | B | | 6 | Jerry | Howard | A | | 7 | Willie | Nelson | C | | 8 | Juan | Perry | A | | 9 | Victor | Allen | B | +-----------+-----------+----------+----------+ SPECIALTIES +--------------+-----------+ | specialty_id | specialty | +--------------+-----------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | F | | 7 | G | | 8 | H | | 9 | I | +--------------+-----------+ HAS_SPECIALTY +-----------+--------------+ | doctor_id | specialty_id | +-----------+--------------+ | 1 | 4 | | 1 | 6 | | 1 | 8 | | 2 | 3 | | 2 | 8 | | 3 | 1 | | 3 | 4 | | 3 | 5 | | 4 | 4 | | 5 | 8 | | 5 | 9 | | 6 | 2 | | 6 | 7 | | 7 | 9 | | 8 | 4 | | 9 | 2 | | 9 | 3 | +-----------+--------------+ 

Результат должен быть:

 +-----------+-----------+----------+----------+ | doctor_id | firstname | lastname | area | +-----------+-----------+----------+----------+ | 2 | Jane | Brown | B | | 5 | Paula | Lopez | B | +-----------+-----------+----------+----------+ 

Вы можете сделать это с помощью простого INNER JOIN :

 SELECT d.doctor_id, d.firstname,d.lastname,d.area FROM doctors d INNER JOIN has_specialty hs ON d.doctor_id = hs.doctor_id INNER JOIN specialties s ON hs.specialty_id = s.specialty_id WHERE s.specialty = 'H' AND d.area = 'B'; 

Демо-версия sqlfiddle