0

I have 3 tables.

  1. Grades
  2. Disciplines
  3. Students

I want to select only the disciplines that doesn't have grade 2. What am I doing wrong?

SELECT DISTINCT DISC_NAME
FROM STUDENTS, DISCIPLINES, GRADES
WHERE 
GRADES.ID= STUDENTS.ID
AND
GRADES.KOD_DISC = DISCIPLINES.DISC_ID
GROUP BY GRADE
HAVING
GRADE != 2
4
  • 1
    Please check these suggestions.
    – mustaccio
    Mar 27, 2021 at 14:03
  • 1
    Create a db<>fiddle or similar with enough sample data to demonstrate the problem. There is an easy solution to your problem, the hazzle is to figure out what your tables look like Mar 27, 2021 at 14:43
  • 1
    Hi, and welcome to the forum! A schema like this might be of help - adapted from here. It's always good to provide a fiddle - as @Lennart suggested. Also important, (and it's especially so for MySQL - lots of new features recently!), is to provide your server version either as a tag or prominently in the text of your question.
    – Vérace
    Mar 27, 2021 at 19:52
  • Vérace, seems like this is the right method, great. Mar 28, 2021 at 20:27

1 Answer 1

1

HAVING is for filtering on aggregated results. You could use it to solve your problem if you rewrite it slightly so it's filtering on the number of times a DISC_NAME has a result from your joins which has GRADE=1. For this aggregate, you'd be grouping by the DISC_NAME and you can count matches with a case when

select d.disc_name
from students s
    ,disciplines d
    ,grades g
where g.id= s.id
and   g.kod_disc = d.disc_id
group by d.disc_name
having sum(case when g.grade = 2 then 1 else 0 end) = 0

(assuming the grade column comes from grades and disc_name is part of disciplines)

You could also achieve the same with an anti-join query. Here I've removed the join to students because you don't seem to use it (other than making sure the grade row belongs to a student row, which I would assume isn't necessary). I've also switched to using more modern ansi join syntax - you could use either.

select d.disc_name
from disciplines d
where not exists (select null
                  from   grades g
                  where  d.disc_id = g.disc_id
                  and    g.grade = 2
                 )
1
  • 1
    I explained it bad and not detailed enough, but your querry worked nice. Mar 27, 2021 at 16:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.