Major_applied_for table
| id | preference | application_number (fk) | major_code (fk) |
|---|---|---|---|
| 1 | 1 | 2 | 1 |
| 2 | 1 | 1 | 1 |
| 3 | 3 | 3 | 1 |
| 4 | 2 | 1 | 2 |
| 5 | 2 | 2 | 2 |
Some Clarifications:
• The code attribute of Major table holds (1)CS for computer science, (2)BMS for business management and so on.
• preference attribute of Major_Applied_For is 1, 2 or 3 (1 for being the first choice, 2 being the second choice and 3 being the third choice) . . .
This is a table that many to many relationship resolved in, I wanna get all the application numbers that have CS as the first choice and BMS as the second choice.
I tried this sql statement but it's logically incorrect.
SELECT m.id, CONCAT(m.fname, " ", m.lname) AS Fullname, app.number AS application_no FROM applicant m, application app, major_applied_for mjaf WHERE ((mjaf.major_code = 1 AND mjaf.preference = 1) AND (mjaf.major_code = 2 AND mjaf.preference = 2) AND (mjaf.application_number = app.number AND app.applicant_id = m.id));
How can I resolve this issue?
Find the applicants with CS as their first pref, find the applicants with BMS their second pref. Inner join these two sets and check their names.
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