create a view from 3 different table

Question

i'm looking for some help please, here is my situation. i have three tables.

table 1

kpi_id , kpi_name, description_kpi

table 2

prog_id , prog_name, prog_description

kpi_per_prog

prog_id, kpi_id

now i want to creat a view from kpi_per_prog where i can have

result_table

id_prog , id_kpi, prog_name, kpi_name

kpi_id and prog_id are the primary keys

Answer 1

Please try like this:

select r.prog_id , r.kpi_id from 
   result_table r join table1 t1 on (t1.kpi_id = r.kpi_Id) 
   join table2 t2 on (t2.prog_id = r.prog_id)

Answer 2

Something like the following should work.

CREATE VIEW KPIVIEW
SELECT 
    kpp.*, t2.prog_name, t1.kpi_name
FROM
    kpi_per_prog kpp
        LEFT JOIN
    table1 t1 ON kpp.kpi_id = t1.kpi_id
        LEFT JOIN
    table2 t2 ON kpp.prog_id = t2.prog_id;