如何基于两列上的两个条件进行SQL查询

Question

请参阅EARD场景

主要_应用_表格

application_number<1><1><1>

id	首选项
1	1	2
2	1	1
3	3	3	1
4	2	2
5	2	2	2

找到第一个首选CS的申请人，找到第二个首选BMS的申请人。内部将这两个集合连接起来，并检查他们的姓名。

with first_cs as (
select c.applicant_id
from major_applied_for a inner join major b on a.major_code = b.code and a.preference = 1 and b.name = 'CS'
inner join application c on a.application_number = c.number ), second_bms as(
select c.applicant_id
from major_applied_for a inner join major b on a.major_code = b.code and a.preference = 2 and b.name = 'BMS'
inner join application c on a.application_number = c.number ) select fname,
lname from first_cs a inner join second_bms b on a.applicant_id = b.applicant_id
inner join applicant c on a.applicant_id = c.id