query uurgent!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: query uurgent!!

  1. #1
    Join Date
    Jun 2001
    Posts
    109
    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

  2. #2
    Join Date
    Apr 2002
    Posts
    4
    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
  •  


Click Here to Expand Forum to Full Width