CODEWITHSUNDEEP
sqloracle-databaseleft-joininner-join
Doodle
Doodle

Reputation: 479

Oracle SQL Query to join multiple tables

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

Upvotes: 1

Views: 1160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 1

Related Questions