Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inner Join Three Tables

I am working in Oracle APEX.i want to make a report from three tables through INNER JOIN .The Tables are as Fallows.

PATIENT (Par_Id(Pk),Pat_Name,Pat_Gender)

HISTORY (His_Id(Pk),Pat_id(Fk),Treated_By)

and

Treatment ( Treat_Id, His_id(Fk),Pat_id(Fk) ,Treat_Type ,Charges)

I want to display all the columns in Report mentioned in the above three Tables.

Thanks.

like image 673
Usman YousafZai Avatar asked Jan 10 '13 09:01

Usman YousafZai


2 Answers

You should always specify the columns to return, especially as the tables contain identical column names

SELECT p.Par_Id, p.Pat_Name, p.Pat_Gender,
    h.His_Id, h.Treated_By,
    t.Treat_Id, t.Treat_Type, t.Charges
FROM Patient p 
INNER JOIN History h 
    ON p.PAR_ID = h.PAT_ID
INNER JOIN Treatment t
    ON h.HIS_ID = t.HIS_ID AND p.PAR_ID = h.PAT_ID
like image 154
Rhumborl Avatar answered Oct 06 '22 21:10

Rhumborl


This should do the trick

SELECT * FROM Patient p 
    INNER JOIN History h 
        ON p.PAR_ID = h.PAT_ID
    INNER JOIN Treatment t
        ON h.HIS_ID = t.HIS_ID AND p.PAR_ID = h.PAT_ID
like image 38
Tikkes Avatar answered Oct 06 '22 21:10

Tikkes