question

wifi265 avatar image
wifi265 asked

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"))
joinleft joinjoin hints
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If this is SQL Server, then it sounds as though you might want to look at "EXCEPT"

0 Likes 0 ·

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.