CODEWITHSUNDEEP
sqloracle-database
Witty Gritty
Witty Gritty

Reputation: 7

Create view by joining three tables in SQL

I have three tables STUDENTS, SUBJECTS, RANK ,with data as -

1) STUDENTS [NAME(Primary)]

NAME
--------
Alex
Greg

2) SUBJECTS [ID(Primary)]:

ID
--------
100
101
102

3) RANK [SEQ(Primary), NAME, ID, RANK]

SEQ    NAME    ID     RANK
------ ------- ------ ------
1      Alex    100    A 
2      Greg    100    A 
3      Greg    101    B

I want to create a view that should display data as

NAME    ID     RANK
------- ------ ------
Alex    100    A
Alex    101    Z
Alex    102    Z
Greg    100    A
Greg    101    B
Greg    102    Z

So, for every student and for every subject, the View should display the RANK if present in RANK table, else replace the NULL with 'Z'.

I'm a newbie to SQL. So any help in forming the query would be deeply appreciated!

Upvotes: 0

Views: 900

Answers (2)

Deep
Deep

Reputation: 3202

cross join student and subject then left outer join the result with rank to get ranks for all (student, subject) combination. selecting column with NVL OR COALESCE will replace NULL with 'z'.

SELECT st.name,
       su.id,
       NVL(ra.rank,'Z')           Rank, --COALESCE(ra.rank,'Z') Rank
FROM   student st
       CROSS JOIN subject su
       LEFT OUTER JOIN rank ra
                    ON ra.name = st.name
                       AND ra.id = su.id
ORDER  BY st.name,su.id

Note : ORDER BY can be removed from above query if you don't need.

fiddle

Upvotes: 1

jherran
jherran

Reputation: 3367

SELECT r.NAME, r.ID, NVL(r.RANK, 'Z')
FROM RANK r, studendts st, SUBJECTS su
WHERE st. NAME = r. NAME
AND   su.ID = r.ID
ORDER BY 1,2,3

Upvotes: 0

Related Questions