All we need is an easy explanation of the problem, so here it is.
I have 3 tables.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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
grade column comes from
disc_name is part of
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 )
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂