数据关联查询是很常见的,如何根据一个表的查询结果字段,去判断是否要查另外的一张或多张表呢;
如下,举例:
4个表,现要查询配送任务,根据机构和业务表判断需不需要三证,如果需要就查询单证表,反之不查!
根据left join 和 case when 搞定:
SELECT t.delievery_task_id FROM ds_delivery_task t
LEFT JOIN ds_credential c ON t.delievery_task_id = c.delivery_task_id
LEFT JOIN ds_business_source_conf b ON t.bz_source_code =b.bz_source_code
LEFT JOIN ds_department_conf d ON t.department_code = d.department_conf_id
WHERE t.delievery_task_id = (
CASE WHEN b.is_credential_must = '1' OR d.is_credential_need ='1'
THEN c.delivery_task_id
ELSE t.delievery_task_id
END
);