SQL MINUS to LEFT JOIN

Question

question

wifi265 asked May 4, '20

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"))

join left join join hints

1

10 |1200

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

ThomasRushton ♦♦ · May 04, 2020 at 04:19 PM

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

0 ·

Answer 1 · 2020-05-04T16:19:45Z

Jon Crawford answered May 4, '20

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

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

question