Left join with condition is failing in oracle

Hi Expert,

I am looking for left join only
i want all records from transaction table.
and it is having where condition as well

looking better optimised solution

select 
trans.deal_id,
    trans.deal_rec_id,
    trans.supplier_doc_number    
	FROM transaction trans 
LEFT JOIN  details  details ON details.DEAL_REC_ID=trans.DEAL_REC_ID 
AND details.SUPPLIER_CR_AMOUNT > 0 
where trans.CODE  IN ('NEW','UPDATE')

Select allOpen in new window

your requirement is kinda not clear enough for me, if you mean to put in the conditions at the "right side" table, while selecting all records from the "main" table, then you should try use sub-query (as already mentioned in sample I have posted):

select 
trans.deal_id,
    trans.deal_rec_id,
    trans.supplier_doc_number    
	FROM transaction trans 
LEFT JOIN （select * from details where SUPPLIER_CR_AMOUNT > 0 ) details ON details.DEAL_REC_ID=trans.DEAL_REC_ID 
where trans.CODE  IN ('NEW','UPDATE')

Select allOpen in new window

this is not working
LEFT JOIN （select * from details where SUPPLIER_CR_AMOUNT > 0 ) details ON details.DEAL_REC_ID=trans.DEAL_REC_ID


still i am getiing the records from SUPPLIER_CR_AMOUNT  value is 0

Btw: You're selecting columns from the "main" table only + having a left join = makes NO SENSE! In that case, just leave out the left join. If you're not doing anything with that left joined table, just remove it ;-)

As others have said, what you are asking for doesn't make sense.

With the LEFT JOIN, what you are asking for is all records in the TRANSACTION table (regardless of any information in the DETAILS table).

If you really only want records from the TRANSACTION table where the DETAILS.SUPPLIER_CR_AMOUNT > 0, then just change the LEFT JOIN to a JOIN.

If that isn't what you want, you need to explain it better.  Sample data and expected results will go a long way to explaining what you want.

Based on your description, the LEFT JOIN is not failing, it is doing exactly what it is supposed to do.

If you change the where clause from this:

details.SUPPLIER_CR_AMOUNT > 0

to this:

nvl(details.SUPPLIER_CR_AMOUNT, 0) > 0

Does that give you the results you want?  If so, then that is a straight join.  There is no need for a LEFT JOIN at all.

still i am getiing the records from SUPPLIER_CR_AMOUNT  value is 0

yea, perhaps we don't have any clues on that.

can you provide sample data for the issues?

still i am getiing the records from SUPPLIER_CR_AMOUNT  value is 0                                    

How can you even tell, if you're just selecting columns from the trans table??!! -> Impossible!!

As the others have said, it doesn't make sense to use both a "left join" and a filter condition on the "left joined" table.  If you want to use a filter condition on the details table, then you cannot do a left join to that table.

Or, if you want a left join to that table (so all records from the transaction table get returned, whether there is a matching record in the detail table or not) then you cannot include a filter on the details table records.

Why? That filter on the details table only applies to records that actually exist in the details table.  A "left join" tells Oracle to include the details table even if there are no matching records there, but it is impossible for Oracle to apply your filter (Where SUPPLIER_CR_AMOUNT > 0) on records that don't even exist in that table.

I have to fetch two type of codes from the  table transaction.
"New" directly I can take from main table.
"update" will have entry in transaction and details tables both.

SUPPLIER_CR_AMOUNT value based on this value I have to fetch the "update" code value.

this SUPPLIER_CR_AMOUNT will have null,0 and some amount will be there.
this SUPPLIER_CR_AMOUNT available only in details table.

both tables having different codes as well.new,update,credit,debut,...

i need only those 2 codes alone.
from the details table I am not selecting any data.SUPPLIER_CR_AMOUNT>0
then fetch the data.


just checking the

yes i understood.
I should not use left join.i have to try with nested query.

Thanks