3 Attachment(s)
Need Help in Select Query
I have two tables named TBL_PAYMENT_TRN & TBL_PROFIT_PETROLEUM_TRN. I want to select all the records from the table TBL_PROFIT_PETROLEUM_TRN & the matching records from TBL_PAYMENT_TRN.
In Figure 1 ,TBL_PAYMENT_TRN is dere.
In Figure 2,TBL_PROFIT_PETROLEUM_TRN is dere.
In Figure 3, See my Select Query Result
Code:
select TBL_PAYMENT_TRN.UTR_DD_NO,TBL_PROFIT_PETROLEUM_TRN.ACCOUNT_ID from TBL_PROFIT_PETROLEUM_TRN,TBL_PAYMENT_TRN where TBL_PROFIT_PETROLEUM_TRN.ACCOUNT_ID= TBL_PAYMENT_TRN.TRANSACTION_ID
AND
TBL_PAYMENT_TRN.TYPE_OF_PAYMENT_ID=3
In TBL_PAYMENT_TRN one entry is made against ACCOUNT_ID of TBL_PROFIT_PETROLEUM_TRN. So above query selects dat.
In TBL_PROFIT_PETROLEUM_TRN another entry is dere for ACCOUNT_ID - 378. I also wnat to select dat record & if there is no corresponding record in TBL_PAYMENT_TRN against dat ACCOUNT_ID,I want the null to come.
How to do that!Help me out.
I want the query in Oracle Plz Reply.
I want the output to be like
Code:
UTR_DD_NO ACCOUNT_ID
utr1 379
null 378
Re: Need Help in Select Query
Use a Left Join.
Code:
select TBL_PAYMENT_TRN.UTR_DD_NO,TBL_PROFIT_PETROLEUM_TRN.ACCOUNT_ID
From TBL_PROFIT_PETROLEUM_TRN
LEFT JOIN TBL_PAYMENT_TRN On TBL_PROFIT_PETROLEUM_TRN.ACCOUNT_ID= TBL_PAYMENT_TRN.TRANSACTION_ID AND TBL_PAYMENT_TRN.TYPE_OF_PAYMENT_ID=3
Which version of Oracle are you using? Older versions don't support the LEFT JOIN keywords. You will need to use *=, the old syntax of a left join.
Re: Need Help in Select Query
hi brucevde,thx very much!!!I want if there is no record only for column UTR_DD_NO,then in place of null 0 should be there!!!
Re: Need Help in Select Query
Use either the Coalesce or NVL functions. They basically do the same thing.
Select Coalesce(TBL_PAYMENT_TRN.UTR_DD_NO,0), ...
Select NVL(TBL_PAYMENT_TRN.UTR_DD_NO,0), ...