CODEWITHSUNDEEP
sqlsql-serverjoin
dionajie
dionajie

Reputation: 404

JOIN Table no duplicate column

I have 4 tables like this

Student Table

-----------------------
|id_student | name    |
|1          | John    |
|2          | Michael |
-----------------------

Phone Number Table

--------------------------------------
|id_phone | phone_number | id_student |
| 1       |  +612823888  | 1          |
| 2       |  +612823723  | 1          |
| 3       |  +612123123  | 2          |
| 4       |  +613432322  | 2          |
---------------------------------------

Course Table

____________________
|id_course | course  |
|1         | Math    |
|2         | Science |
|3         | Art     |
|__________|_________|

Take Course Table

__________________________________
|id_take | id_student | id_course |
|1       | 1          | 1         |
|2       | 1          | 2         |
|3       | 1          | 3         |
|4       | 2          | 1         |
|________|____________|___________|

=== View I Expected ====

 _________________________________
|name    |phone        |course   |
|John    | +6128238883 | Math    |
|John    | +6128237237 | Science |
|John    | -           | Art     |
|Michael | +612123123  | Math    |
|Michael | +613432322  | -       |
|________|_____________|_________|

SQL syntax I create : 

SELECT student.name, phone.phone_number, course.course_name FROM student
JOIN phone ON phone.id_student = student.id_student
JOIN take_course ON take_course.id_student = student.id_student
JOIN course ON course.id_course = take_course.id_course

There is problem when JOIN Table, because phone_number table just JOIN to student_table and will duplicate in table I expected. What is right SQL Syntax to create view like that?

Upvotes: 0

Views: 50

Answers (1)

Squirrel
Squirrel

Reputation: 24803

Use row_number() to generate a sequence ID and then use FULL OUTER JOIN on phone and take-course/course ON id_student & the "sequence id"

select  s.name, pc.phone_number, pc.course
from    Student s
        inner join
        (
            select  id_student  = coalesce(p.id_student, c.id_student),
                    p.phone_number,
                    c.course
            from    
            (
                select  p.id_student, p.phone_number,
                        -- id is the new sequence id
                        id = row_number() over (partition by p.id_student 
                                                    order by p.id_phone)
                from    Phone_Number p  
            ) p 
            full outer join
            (
                select  t.id_student, c.course,
                        -- id is the new sequence id
                        id = row_number() over (partition by t.id_student 
                                                    order by c.id_course)
                from    Take_Course t
                        inner join Course c on  t.id_course  = c.id_course
            ) c                             on  p.id_student = c.id_student
                                            and p.id         = c.id
        ) pc                                on  s.id_student = pc.id_student
order by s.name

Upvotes: 1

Related Questions