Помогите написать запрос.
У меня есть три таблицы:
+-------------------+
| Patient |
| PatientPhysician |
| Physician |
+-------------------+
Найдите пациентов, у которых имя, фамилия и должность в пределах одного PhysicianOrganizationId совпадают .
Я покажу вам данные, чтобы вы лучше поняли проблему:
mysql> SELECT pt.Id, pt.FirstName, pt.LastName, pt.DoB, ph.PhysicianOrganizationId
-> FROM Patient pt, Physician ph, PatientPhysician pp
-> WHERE pt.Id = pp.IdPatient AND ph.Id = pp.IdPhysician
-> ORDER BY pt.Id;
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 2 | Mario | Gotze | 1989-01-09 | 102 |
| 3 | Mario | Gotze | 1989-01-09 | 101 |
| 4 | Fillip | Gotze | 1989-01-09 | 101 |
| 5 | Marco | Rues | 1988-09-12 | 102 |
| 5 | Marco | Rues | 1988-09-12 | 101 |
| 5 | Marco | Rues | 1988-09-12 | 103 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 7 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 102 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
+----+-----------+-------------+------------+-------------------------+
15 rows in set (0.01 sec)
Я написал запрос, но он выдает неправильные результаты:
SELECT
pt.Id,
pt.FirstName,
pt.LastName,
pt.DoB,
ph.PhysicianOrganizationId
FROM Patient pt, Physician ph, PatientPhysician pp
WHERE pt.Id = pp.IdPatient AND ph.Id = pp.IdPhysician
GROUP BY pt.FirstName, pt.LastName, pt.DoB, ph.PhysicianOrganizationId
HAVING COUNT(*) > 1 ORDER BY pt.Id;
Полученные результаты:
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
+----+-----------+-------------+------------+-------------------------+
Мне нужен этот результат:
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 3 | Mario | Gotze | 1989-01-09 | 101 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 7 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 101 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
+----+-----------+-------------+------------+-------------------------+
Подскажите что я делаю не так?