-
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
-
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.
Dilesh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|