'How can I do SQL query based on two conditions on two columns?
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|