Oracle SQL Query to join multiple tables

Question

I am trying to join multiple tables and the below query works well.

select 
    e.ENCNTR_ID e_id
    , p.PERSON_ID p_id
    , e.REG_DT_TM admitted


from   
    episode e 
    , patient  p 
where (e.reg_dt_tm > '01/JAN/2018' 
       and e.reg_dt_tm < '02/JAN/2018' 
       and e.active_ind = 1
       and e.encntr_type_cd = 123.00 
       and e.ACTIVE_STATUS_CD = 1234 
       and e.person_id = p.person_id)

But when I change it and add more tables it gives me the error

"SQL Command not properly ended"

I need to add conditions on the first table(episode) as otherwise the query runs very slow.

select 
    e.ENCNTR_ID e_id
    , p.PERSON_ID p_id
    , e.REG_DT_TM admitted
    , ce.EVENT_ID event_id

from   
    ENCOUNTER e 
    , person  p 
where (e.reg_dt_tm > '01/JAN/2018' 
       and e.reg_dt_tm < '02/JAN/2018' 
       and e.active_ind = 1
       and e.encntr_type_cd = 7113.00 
       and e.ACTIVE_STATUS_CD = 22223 
       and e.person_id = p.person_id)
left join CLINICAL_EVENT ce on ce.ENCNTR_ID = e.ENCNTR_ID      
                            and ce.EVENT_CD in (1235764 
                                                ,22161234 
                                                )                  
                            and ce.valid_until_dt_tm > sysdate
left join CE_BLOB cb on ce.EVENT_ID = cb.EVENT_ID
                    and cb.valid_until_dt_tm > sysdate

order by e.REG_DT_TM, ce.PERFORMED_DT_TM, ce.CLINICAL_EVENT_ID

Answer 1

The query should look like this:

select e.ENCNTR_ID as e_id, p.PERSON_ID as p_id, e.REG_DT_TM as admitted, ce.EVENT_ID as event_id
from ENCOUNTER e join
     person p 
     on e.person_id = p.person_id left join
     CLINICAL_EVENT ce
     on ce.ENCNTR_ID = e.ENCNTR_ID and   
        ce.EVENT_CD in (1235764, 22161234) and                
        ce.valid_until_dt_tm > sysdate left join
     CE_BLOB cb
     on ce.EVENT_ID = cb.EVENT_ID and
        cb.valid_until_dt_tm > sysdate
where e.reg_dt_tm > date '2018-01-01' and
      e.reg_dt_tm < date '2018-01-02' and
      e.active_ind = 1 and
      e.encntr_type_cd = 7113.00 and
      e.ACTIVE_STATUS_CD = 22223 
order by e.REG_DT_TM, ce.PERFORMED_DT_TM, ce.CLINICAL_EVENT_ID;

Notes:

• Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.
• The WHERE clause goes after the FROM clause.
• JOIN is an operator in the FROM clause, so all JOINs need to be before the WHERE.
• Use the keyword DATE for date constants.