Consolidate values from multiple tables without duplicate it

user_4373844 0
Viewed 70

I have tried to do the below example using joins and union but was not successful. I appreciate any assistance.

I have a Student table and 3 other tables of courses Planed to be done, current Enrolled and, Concluded course. For each of those courses tables, I have the FK_Student and the Course name. I do like to "join" all results in a single line for each course name with each course table as a column name. (The same course can be in multiple tables) See example below:

Table: Student
Id_Student | Student
1           Bob
2 ...

Table: Planed
Id_Planed | Course | Fk_Student
1          History   1
2          English   1
3          Biology   1
4          Geometry  1
5          PE        1
6          Algebra   1
....

Table: Enrolled
Id_Enrolled | Enrollment | Fk_Student
1            History        1
2            Biology        1
3            PE             1
...

Table: Concluded
Id_Concluded | Conclusion | Fk_Student
1             History      1
2             English      1
3             Physics      1
...

Expected Result:
Student | Planed | Enrolled | Concluded
Bob      History   History   History
Bob      English   NULL      English      
Bob      Biology   Biology   NULL       
Bob      Geometry  NULL      NULL
Bob      PE        PE        NULL
Bob      Algebra   NULL      NULL
Bob      NULL      NULL      Physics
sql-server sql
3 Answers

FULL OUTER JOIN is used here because every subject name are not existed in all table. First subquery retrieve student wise Course, Enrollment and Conclusion record. Then INNER JOIN with student table as per expected output. If all student info needed then LEFT JOIN will be better. In Planed/Enrolled/Concluded table same Course/Enrollment/Conclusion can not assigned multiple time for particular student. As student_id and course needed for calculation so use two tables student_id and course inside COALESCE() so that always return NOT NULL value.

-- SQL SERVER (v2014)
SELECT s.Student, r.Course, r.Enrollment, r.Conclusion
FROM Student s
INNER JOIN (SELECT COALESCE(t.student_id, c.Fk_Student) student_id
                 , t.Course, t.Enrollment, c.Conclusion
            FROM (SELECT COALESCE(p.Fk_Student, e.Fk_Student) student_id
                       , COALESCE(p.Course, e.Enrollment) Course_t
                       , p.Course
                       , e.Enrollment
                  FROM Planed p
                  FULL OUTER JOIN Enrolled e
                               ON p.Fk_Student = e.Fk_Student
                              AND p.Course = e.Enrollment) t
                  FULL OUTER JOIN Concluded c
                               ON c.Fk_Student = t.student_id
                              AND c.Conclusion = t.Course_t) r
        ON s.Id_Student = r.student_id;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=09d03c0b64d31c8ae5a3b91145b7b7e5

user_163646250

It looks like your data model is somewhat flawed in its design, I would have expected a table of Courses with each course linked by a Course_Id to each student for each type.

I'm not clear on your desired output but it seems like you want a complete list of courses for each student and which of each are applicable in each case.

You can use a CTE to build a table of truth for all courses which is then cross-joined to Students so each student is presented with the full list of courses and then outer-joined to the 3 tables to indicate which courses are applicable to the student on each case.

with courses as (
    select course from planed union
    select enrollment from enrolled union
    select conclusion from concluded
)
select s.Student, p.Course Planed, e.Enrollment Enrolled, cc.Conclusion Concluded
from courses c
cross join student s 
left join planed p on p.course=c.course and p.fk_student=s.id_student
left join enrolled e on e.enrollment=c.course and e.fk_student=s.id_student
left join concluded cc on cc.Conclusion=c.course and cc.fk_student=s.id_student
user_153326500

Try this (no nested query or CTEs):

SELECT
  s.Student,
  p.Course,
  e.Enrollment,
  c.Conclusion
FROM Planed AS p
FULL JOIN Enrolled AS e
  ON e.Fk_Student = p.Fk_Student AND
     e.Enrollment = p.Course
FULL JOIN Concluded AS c
  ON (c.Fk_Student = p.Fk_Student AND
      c.Conclusion = p.Course) OR
     (c.Fk_Student = e.Fk_Student AND
      c.Conclusion = e.Enrollment)
RIGHT JOIN Student AS s
  ON s.Id_Student IN (
       p.Fk_Student,
       e.Fk_Student,
       c.Fk_Student
     );

Result:

+---------+----------+------------+------------+
| Student |  Course  | Enrollment | Conclusion |
+---------+----------+------------+------------+
| Bob     | History  | History    | History    |
| Bob     | English  |            | English    |
| Bob     | Biology  | Biology    |            |
| Bob     | Geometry |            |            |
| Bob     | PE       | PE         |            |
| Bob     | Algebra  |            |            |
| Bob     |          |            | Physics    |
| Sam     |          |            |            |
+---------+----------+------------+------------+

db<>fiddle

user_33504280
Related