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
0
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
这里的例子
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
0
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
这里的例子
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)