我有一个SELECT语句，用于从学生数据库的地址表中提取父级联系人信息。我需要定义查询以仅返回当前学生的家长信息（student_group ='Student'），但是查询将忽略此查询并返回所有学生组的信息（例如，“ Graduate”，“ Withdrawn”等）。您能在这里看到我做错了什么吗？谢谢。
SELECT DISTINCT
--Name 1 in P1 household
s.id
,a.name1_web_user_id as contact_1_id
,a.name1_full_name as contact_1_name
,case 'Name1:Mobile'
when a.Other_No_Type_1 then a.Other_No_1
when a.Other_No_Type_2 then a.Other_No_2
when a.Other_No_Type_3 then a.Other_No_3
when a.Other_No_Type_4 then a.Other_No_4
end as contact_1_mobile
,a.email as contact_1_email
--Name 2 in P1 household
,a.name2_web_user_id as contact_2_id
,a.name2_full_name as contact_2_name
,case 'Name2:Mobile'
when a.Other_No_Type_1 then a.Other_No_1
when a.Other_No_Type_2 then a.Other_No_2
when a.Other_No_Type_3 then a.Other_No_3
when a.Other_No_Type_4 then a.Other_No_4
end as contact_2_mobile
,a.email_2 as contact_2_email
--Name 1 in P2 household
,s.id
,b.name1_web_user_id as contact_3_id
,b.name1_full_name as contact_3_name
,case 'Name1:Mobile'
when b.Other_No_Type_1 then b.Other_No_1
when b.Other_No_Type_2 then b.Other_No_2
when b.Other_No_Type_3 then b.Other_No_3
when b.Other_No_Type_4 then b.Other_No_4
end as contact_3_mobile
,b.email as contact_3_email
--Name 2 in P2 household
,b.name2_web_user_id as contact_4_id
,b.name2_full_name as contact_4_name
,case 'Name2:Mobile'
when b.Other_No_Type_1 then b.Other_No_1
when b.Other_No_Type_2 then b.Other_No_2
when b.Other_No_Type_3 then b.Other_No_3
when b.Other_No_Type_4 then b.Other_No_4
end as contact_4_mobile
,b.email_2 as contact_4_email
FROM rg_student s
left outer join rg_addr a on s.id=a.id
AND a.addr_code='P1' AND a.rg_active = 'Y'
AND s.id in(SELECT id from rg_student where student_group='Student')
left outer join rg_addr b on s.id=b.id
AND a.addr_code='P2' AND b.rg_active = 'Y'
AND s.id in(SELECT id from rg_student where student_group='Student')
我简化了FROM子句，在此过程中丢失了contact_3和contact_4数据的输出。这是我现在所拥有的：
FROM rg_student s
left outer join rg_addr a on s.id=a.id
and a.addr_code='P1' AND a.rg_active = 'Y'
left outer join rg_addr b on s.id=b.id
and a.addr_code='P2' AND b.rg_active = 'Y'
WHERE s.student_group = 'Student'
您想要选择所有当前学生，因此从学生表中选择并使用“ WHERE”仅选择当前学生。
您希望使用地址进行联接，因此联接并在ON子句中指定联接条件。
select
...
FROM rg_student s
LEFT JOIN rg_addr p1 ON p1.id = s.id AND p1.addr_code = 'P1' AND p1.rg_active = 'Y'
LEFT JOIN rg_addr p2 ON p2.id = s.id AND p2.addr_code = 'P2' AND p2.rg_active = 'Y'
WHERE s.student_group = 'Student';
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句