Select distinct values from tables in sql

Question

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?

Are you saying you have duplicate ID numbers?

David Starkey
– David Starkey

2013-06-28 15:20:19 +00:00
Commented Jun 28, 2013 at 15:20 — David Starkey
– David Starkey, Commented Jun 28, 2013 at 15:20

juergen d · Accepted Answer · 2013-06-28 15:18:22Z

1

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

answered Jun 28, 2013 at 15:18

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Moldovan Over a year ago

Thank you. I knew about the DISTINCT method but didn't know how to use it

Ian Kenney · Accepted Answer · 2013-06-28 15:20:05Z

0

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

answered Jun 28, 2013 at 15:20

Ian Kenney

6,4561 gold badge27 silver badges45 bronze badges

Collectives™ on Stack Overflow

Select distinct values from tables in sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related