SQL MINUS to LEFT JOIN
Hello there,
is there any options to turn this code with minus to left join code? Because my database doesnt allow MINUS operator.
Here is my code so far
SELECT studenti.jmeno, studenti.prijmeni (( SELECT studenti.jmeno, studenti.prijmeni FROM studenti WHERE rocnik=1 ) MINUS (SELECT studenti.jmeno, studenti.prijmeni FROM studenti JOIN studenti_predmety USING (id_student) JOIN predmety USING (id_predmet) WHERE studenti_predmety.datum="2020-04-07" AND studenti.rocnik=1 AND predmety.zkratka="MAT1"))
If this is SQL Server, then it sounds as though you might want to look at "EXCEPT"
1. You could try EXCEPT, I think that's what you're going for.
2. What DBMS are you using?
3. if SQL Server, then you can use just the second set of your data, sort of. LEFT JOIN to your other tables, put your conditions in the JOIN, not the WHERE, and in WHERE look for IS NULL
SELECT
studenti.jmeno, studenti.prijmeni FROM student
LEFT JOIN predmety ON student.id_student = predmety.id_student AND predmety.zkratka="MAT1"
LEFT JOIN studenti_predmety ON student.id_student = studenti_predmety.id_student
AND studenti_predmety.datum="2020-04-07"
WHERE
studenti.rocnik=1
AND predmety.id_student IS NULL
AND student_predmety.id_student IS NULL
