Home » RDBMS Server » Server Administration » Self Join- Corelated subquery
Self Join- Corelated subquery [message #370838] Tue, 15 February 2000 02:37 Go to next message
vimal
Messages: 46
Registered: February 2000
Member
I have a master table ( t_master) and a transaction table (t_tran). The t_master contains the customer details. The t_tran contains the order details.
I want the list of customers, name and no_of_transactions of all the customers who have done less than 50 transactions in the last 30 days.

Thank you.
Re: Self Join- Corelated subquery [message #370840 is a reply to message #370838] Tue, 15 February 2000 06:20 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
Vimal,
SELECT a.company_id, a.name, count (b.tran_id)
FROM t_master a,
t_tran b
WHERE b.company_id = a.company_id
AND b.tran_date > sysdate - 30
AND 50 > ( SELECT count (c.tran_id)
FROM t_tran c
WHERE c.company_id = a.company.id
AND c.tran_date > sysdate - 30 )
;
should work when you substitute correct column names.
Regards,
Paul
Previous Topic: Problems with dbms_sql.parse (ora-6571)
Next Topic: more précise : convert from Unix date to DD/MM/YY
Goto Forum:
  


Current Time: Thu Mar 28 03:22:25 CDT 2024