query uurgent!!

[saritha]

Hello all,
I need a query .

I have 3 tables ts_payments, ts_transactions, ts_line_items.
These three table have a common column called tc_tran_receipt_no. I need a single column from each of these tables.
The relation between these table are

TC_LINE_ITEMS TABLE

PRIMARY KEY (tc_line_id,tc_tran_receipt_no),
FOREIGN KEY (tc_tran_receipt_no)
REFERENCES dayton.tc_transactions(tc_tran_receipt_no)


TC_PAYMENTS

PRIMARY KEY (tc_pay_id,tc_tran_receipt_no),
FOREIGN KEY (tc_tran_receipt_no)
REFERENCES dayton.tc_transactions(tc_tran_receipt_no)
)


TC_TRANSACTIONS
PRIMARY KEY (tc_tran_receipt_no),
FOREIGN KEY (tc_con_station_id)
REFERENCES dayton.tc_configuration(tc_con_station_id)
)


I need tc_pay_amount from tc_payments table,tc_tran_date from tc_transactions, tc_line_rev_Acct_no from tc_line_items.


This is what I gave


select tc_payments.tc_tran_receipt_no, tc_pay_amount,tc_tran_Date
from tc_payments, tc_transactions,tc_line_items
where tc_payments.tc_tran_receipt_no in(select tc_tran_receipt_no from tc_line_items
where tc_tran_receipt_no in(select tc_tran_receipt_no
from tc_transactions
where tc_tran_date='02-JaN-01'))

and tc_payments.tc_tran_receipt_no= tc_transactions.tc_tran_receipt_no

I need tc_line_rev_acct_no in my query when I add that column then it return duplicate rows to me.

Can anyone please help me.

thanks
Saritha

[dilesh]

select tc_payments.tc_tran_receipt_no, tc_pay_amount,tc_tran_Date
from tc_payments, tc_transactions,tc_line_items
where tc_payments.tc_tran_receipt_no in(select tc_tran_receipt_no from tc_line_items
where tc_tran_receipt_no in(select tc_tran_receipt_no
from tc_transactions
where tc_tran_date='02-JaN-01'))

and tc_payments.tc_tran_receipt_no= tc_transactions.tc_tran_receipt_no

Can you try:
Instead of using 'IN' relational object, user the outer join with the three table using your primary key , foreign key relations.

select tc_payments.tc_tran_receipt_no, tc_pay_amount,tc_tran_Date
from tc_payments, tc_transactions,tc_line_items
where ts_transaction.tc_tran_receipt_no=tc_payments.tc_tran_receipt_no(+)
and ts_transaction.tc_tran_receipt_no=tc_line_items.tc_tran_receipt_no(+)

and tc_payments.tc_tran_receipt_no= tc_transactions.tc_tran_receipt_no

as I did not have entire table, I think this will work.