| Self Join- Corelated subquery [message #370838] |
Tue, 15 February 2000 02:37  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
