CODEWITHSUNDEEP
sqldatabaseoracle-databasejoin
MozenRath
MozenRath

Reputation: 10030

SQL implicit Join to explicit join

I have a simple query:

SELECT THDR.filename, 
       THDR.txn_header_id, 
       THDR.txn_header_ext_id, 
       THDR.txn_header_dttm, 
       THDR.upload_dttm, 
       Nvl(Sum(TDTL.txn_vol), 0) TOTAL_VOLUME, 
FROM   ci_txn_header THDR, 
       ci_txn_detail TDTL, 
WHERE  THDR.txn_header_id = TDTL.txn_header_id

now The problem I am facing is that, if There exists a record in the ci_txn_header such that no corresponding record in TDTL exists, then that record is not returned by this query.

Is there a way by which I can include that in my results? I dont want to use a union as I think something better is possible by using an explicit join in the query.

Please help me with exactly which join is needed to solve this issue.

Am using Oracle DB

Upvotes: 3

Views: 189

Answers (1)

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9158

Use LEFT JOIN

SELECT THDR.filename, 
       THDR.txn_header_id, 
       THDR.txn_header_ext_id, 
       THDR.txn_header_dttm, 
       THDR.upload_dttm, 
       Nvl(Sum(TDTL.txn_vol), 0) TOTAL_VOLUME, 
FROM   ci_txn_header THDR 
LEFT JOIN ci_txn_detail TDTL 
ON THDR.txn_header_id = TDTL.txn_header_id

Upvotes: 2

Related Questions