CODEWITHSUNDEEP
sqloracle11goracle-apex
Usman YousafZai
Usman YousafZai

Reputation: 1120

Inner Join of three different Tables

I am working in Oracle APEX. I want to make a report from three different tables (Patient, History, Treatment) through INNER JOIN . 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)

How I am going to display all the mentioned columns of the three Tables in the report.

Thanks.

Upvotes: 8

Views: 84636

Answers (4)

Rhumborl
Rhumborl

Reputation: 16609

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

Upvotes: 30

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

It is too simple in mysql

SELECT 
    * 
FROM PATIENT as p
LEFT JOIN HISTORY as h ON h.Pat_id = p.Pat_Id
LEFT JOIN Treatment as t ON t.His_id = h.His_Id

Upvotes: 1

Tikkes
Tikkes

Reputation: 4689

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

Upvotes: 3

Pranay Rana
Pranay Rana

Reputation: 176896

Try this 

Select * from 
PATIENT  inner join HISTORY  on par_id=HISTORY.Pat_id
inner join Treatment  on par_id=Treatment.Pat_id

Upvotes: 1

Related Questions