-1

I have three tables I want to iterate over. The tables are pretty big so I will show a small snippet of the tables. First table is Students:

id name address
1 John Smith New York
2 Rebeka Jens Miami
3 Amira Sarty Boston

Second one is TakingCourse. This is the course the students are taking, so student_id is the id of the one in Students.

id student_id course_id
20 1 26
19 2 27
18 3 28

Last table is Courses. The id is the same as the course_id in the previous table. These are the courses the students are following and looks like this:

id type
26 History
27 Maths
28 Science

I want to return a table with the location (address) and the type of courses that are taken there. So the results table should look like this:

address type

The pairs should be unique, and that is what's going wrong. I tried this:

select S.address, C.type
from   Students S, Courses C, TakingCourse TC
    where TC.course_id = C.id 
    and S.id = TC.student_id

And this does work, but the pairs are not all unique. I tried select distinct and it's still the same.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
vivalasana
  • 9
  • 3
  • Tag only the database that you use. – forpas Sep 15 '21 at 13:22
  • a student from New York can take a course in History and another student from Miami too can take up the same course, isnt it?. Distinct address,Type will therefore will unique – George Joseph Sep 15 '21 at 13:25
  • Please learn 'modern' `join` syntax. It's the standard for 30-odd years, it is much easier to read, much easier to write, and much easier to maintain. – HoneyBadger Sep 15 '21 at 13:30
  • @GeorgeJoseph Yes both students can take up the same course. But when 2 people from Miami choose history, the results are (Miami, history) and (Miami, history). Only an overview is needed, so that's why I don''t want duplicates – vivalasana Sep 15 '21 at 13:30
  • that is why on using distinct address,type you get a unique pair – George Joseph Sep 15 '21 at 13:31
  • 1
    If you use `SELECT DISTINCT S.address, C.type ...`, you shouldn't get duplicate results for `(Miami, history)`. Maybe there's trailing spaces or case-sensitive issues as play here. – Bruno Sep 15 '21 at 13:32
  • So do you have your answer? – Erwin Brandstetter Sep 23 '21 at 03:33

1 Answers1

1

Multiple students can (and will) reside at the same address. So don't expect unique results from this query.

Only an overview is needed, so that's why I don''t want duplicates

So fold duplicates. Simple way with DISTINCT:

SELECT DISTINCT s.address, c.type
FROM   students s
JOIN   takingcourse t ON s.id = t.student_id
JOIN   courses c ON t.course_id = c.id;

Or to avoid DISTINCT (why would you for this task?) and, optionally, get counts, too:

SELECT c.type, s.address, count(*) AS ct
FROM   students s
JOIN   takingcourse t ON s.id = t.student_id
JOIN   courses c ON t.course_id = c.id
GROUP  BY c.type, s.address
ORDER  BY c.type, s.address;

A missing UNIQUE constraint on takingcourse(student_id, course_id) could be an additional source of duplicates. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228