CODEWITHSUNDEEP
mysqlsql
Paul Moldovan
Paul Moldovan

Reputation: 464

Select distinct values from tables in sql

I have 3 tables:

student (id, name)
discipline (id, name)
student_discipline (stud_id, disc_id, mark)

I need to select everything from students where a student has 5 or more different disciplines with mark > 4

I have this sql:

SELECT * FROM `student_discipline` a
LEFT JOIN `discipline` b ON a.disc_id = b.id
LEFT JOIN `student` c ON a.stud_id = c.id
WHERE a.mark > 4
GROUP BY c.id
HAVING COUNT(b.id) >= 5

This sql gets every student that has promovated 5 or more diciplines but it takes all diciplines even if it's the same twice. I need to get distinct disciplines values. How can I do that?

Upvotes: 0

Views: 2686

Answers (2)

Ian Kenney
Ian Kenney

Reputation: 6426

SELECT DISTINCT  c.id, c.name
FROM `student_discipline` a
LEFT JOIN `discipline` b ON a.disc_id = b.id
LEFT JOIN `student` c ON a.stud_id = c.id
WHERE a.mark > 4
GROUP BY c.id
HAVING COUNT(distinct b.id) >= 5

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Use distinct in your count()

SELECT * 
FROM `student_discipline` a
LEFT JOIN `discipline` b ON a.disc_id = b.id
LEFT JOIN `student` c ON a.stud_id = c.id
WHERE a.mark > 4
GROUP BY c.id
HAVING COUNT(distinct b.id) >= 5

Upvotes: 1

Related Questions