Need Help in Select Query

[sonia.sardana]

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

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

UTR_DD_NO   ACCOUNT_ID
utr1              379
null               378

[brucevde]

Use a Left Join.

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.

[sonia.sardana]

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!!!

[brucevde]

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), ...