-2

I have 3 tables

  1. students (id, name)
  2. student_class_relation (id, student_id, class_id)
  3. class (id, class_name)

a student can have multiple classes associated with him/her

I need to take all student records with class details such as class name, block, etc...

I am getting duplicate rows for students (with one class for each duplicate student)

I used this query

select students.name,class.name, class.block from students
left join student_class_relation on student_class_relation.student_id=students.id 
left join class on class.id=student_class_relation.class_id
Vishnu Roshan
  • 25
  • 2
  • 12
  • 1
    Can you provide a minimal example of your input data and the incorrect output you are getting? – chvndb Jun 20 '22 at 09:28
  • 1
    I think you will find an answer in question - "a student can have multiple classes associated with him/her" - hence "duplicate" records – edekk Jun 20 '22 at 09:28
  • 2
    That's not an error. That is how a ´JOIN` works – schlonzo Jun 20 '22 at 09:41
  • Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For wrong results that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] – philipxy Jun 20 '22 at 10:02
  • Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Jun 20 '22 at 10:03
  • "student records with class details" With what exact format? How is output a function of input? When a student has multiple classes, what row(s) do you want in the result for that student? "I need" What was your exact assignment? – philipxy Jun 20 '22 at 10:18

1 Answers1

0

If you must get single occurrence student id and name you can use string_agg to aggregate each students classes into a single column. However, that makes it extremely difficult if you need to process them.

select stu.id 
     , stu.name
     , string_agg(sclass.class_name,', ') 
  from students stu 
  left join  ( select scr.student_id
                    , cla.class_name  
                 from class  cla
                 join student_class_relation scr 
                   on (cla.id = scr.class_id) 
             ) sclass
          on (stu.id = sclass.student_id) 
    group by stu.id;

NOTE: Having multiple rows with the same column values is not considered a duplicate unless all columns in the row are the same. So a result set with rows 1,1,A and 1,1,B is not a duplicate because the third column is different. That is the different between multiple rows for an id set and duplicate rows. Multiple rows are extremely common.

Belayer
  • 13,578
  • 2
  • 11
  • 22