仅当所有行符合条件时，才将组返回一行

I am Joining Order Table And Test table to get a list against order_number. And am using group by order_number. order_number is repeating in Test table. I want to return the order_number only if the all tests status in Test Table is 5 against the order_number.

我正在加入订单表和测试表以获取针对order_number的列表。并且我按order_number使用group。 order_number在Test表中重复。只有当测试表中的所有测试状态对order_number为5时，我才想返回order_number。

SELECT natera_run_samples.sample_id AS sample_id, 
  natera_runs.job_id AS job_id, 
  order_details.patient_id AS patient_id, 
  order_details.sample_detail_id AS sample_detail_id,
  order_details.order_number AS order_number, 
  order_reaction.reaction_id AS reaction_id, 
  order_reaction.mother_sample_id AS mother_sample_id, 
  order_reaction.plasma_sample_id AS plasma_sample_id, 
  order_reaction.father_sample_id AS father_sample_id, 
  patient_details.age AS age, 
  patient_characteristics.Gestational_age_days AS Gestational_age_days, 
  patient_characteristics.Gestational_age_weeks AS Gestational_age_weeks, 
  panorama_run.id AS id, 
  panorama_run.status AS status, 
  panorama_run.job_id AS pJobId 
  FROM natera_run_samples 
    INNER JOIN natera_runs 
      ON natera_runs.run_id = natera_run_samples.run_id 
    INNER JOIN order_details 
      ON order_details.sample_detail_id = natera_run_samples.sample_id 
    INNER JOIN order_reaction 
      ON order_reaction.order_number = order_details.order_number 
    INNER JOIN patient_details 
      ON patient_details.patient_id = order_details.patient_id 
    INNER JOIN patient_characteristics 
      ON patient_characteristics.patient_id = patient_details.patient_id 
    LEFT JOIN panorama_run 
      ON panorama_run.reaction_id = order_reaction.reaction_id 
  WHERE natera_runs.natera_status = 5 
  GROUP BY order_number

Please help

请帮忙

1 个解决方案

#1

Try this one:

试试这个：

select t.order_number 
  from `order` o 
    inner join test t 
      on o.order_number = t.order_number
  where not exists 
    (select * 
      from test t2 
       where t2.order_number = t.order_number and t2.status <> 5
    )
  group by t.order_number

example here

这里的例子

I would assume that for your query, you would change WHERE natera_runs.natera_status = 5 to be WHERE NOT EXISTS (select * from natera_runs rr where rr.run_id = natera_run_samples.run_id and rr.natera_status <> 5)

我认为对于您的查询，您将更改WHERE natera_runs.natera_status = 5为WHERE NOT EXISTS（从* natera_runs rr中选择* rr.run_id = natera_run_samples.run_id和rr.natera_status <> 5）

#1