Как сделать такой MySQL select из трех таблиц?

Question

[Ninja Mate]

У меня есть 3 таблицы patients(где хранятся данные пациентов), engagements(сервисы но не только пациентам) и patient_services(сервисы тольло для пациентов).
Задача сгруппировать в выборке количество сервисов в локации и сколько уникальных пациентов было обслужено в локации.
Есть вот такой запрос

SELECT ps_user_loc AS loc, COUNT(ps_id) AS number 
FROM patient_services GROUP BY loc 
UNION ALL 
SELECT eng_user_loc AS loc, COUNT(eng_id) AS number FROM engagements WHERE LENGTH( eng_ur )>0 GROUP BY loc

Возвращает все в одной таблице но loc дублируется

CABOOLTURE 354
KILCOY 15
Pine Rivers 352
..
Pine Rivers 115

Как сделать объединение?

Есть вот такой запрос, который возвращает все номера пациентов

SELECT ps_ur AS UR FROM patient_services GROUP BY UR 
UNION ALL 
SELECT eng_ur AS UR FROM engagements WHERE LENGTH( eng_ur )>0 GROUP BY UR

В таблице patient_services есть ps_ur и в engagements есть eng_ur это и есть номера пациентов.

Как сделать запрос который возвращает ответ вида с груп по loc?

loc(без дублей) - все сервисы(count) - уникальные пациенты(count)

Comments

[Ninja Mate]

Есть вот такое квери, но получается, что пациенты не уникальные и loc выдает два раза из двух таблиц

SELECT 
ps_user_loc AS loc, COUNT(ps_id) AS ser, Count(Distinct  ps_ur) AS patient
FROM patient_services GROUP BY loc 
UNION ALL 
SELECT 

eng_user_loc AS loc, COUNT(eng_id) AS ser, Count(Distinct  eng_ur) AS patient 

FROM engagements WHERE LENGTH( eng_ur )>0 GROUP BY loc

Answer 1

[Ninja Mate]

select
  l.loc, 
  sum(coalesce(ps.cnt, 0) + coalesce(e.cnt, 0)) AS ser, 
  count(distinct l.patient) AS patient 
from
(
  select ps_user_loc as loc, ps_ur as patient from patient_services 
  union
  select eng_user_loc as loc, eng_ur as patient  from engagements where length(eng_ur) > 0
) l
left outer join
(
  select
    ps_user_loc as loc, 
    ps_ur as patient,
    count(*) as cnt
  from patient_services
  group by ps_user_loc, ps_ur
) ps on ps.loc = l.loc and ps.patient = l.patient
left outer join
(
  select
    eng_user_loc as loc, 
    eng_ur as patient,
    count(*) as cnt
  from engagements
  where length(eng_ur) > 0
  group by eng_user_loc, eng_ur
) e on e.loc = l.loc and e.patient = l.patient
group by l.loc

Answer 2

[Денис Смирнов]

Вы бы структуру таблиц дали и связи, а то без этого помочь вам затруднительно.

Comments

[Ninja Mate]

посмотрите мой комментарий к вопросу, может станет понятнее. а я сейчас набросаю

[Ninja Mate]

1. patient_services ( eng_id, eng_ur(patient_ur), eng_user_loc )
   
2. engagements ( ps_id, ps_ur(patient_ur), ps_user_loc )
   
3. patients ( patient_ur)
   

фактически нужно посчитать столько записей в engagements и patient_services и сколько в них уникальных patient_ur, не не в каждом отдельно, а сразу во всех