Well, if a "widget" is a field in the order table, then I guess so,
although in that case COUNT(*) should be replaced by SUM(widget). I
thought Grant meant there was a third table "widget" which contained
multiple widget records per order record - ie. a "widget" was a record
in the widget table - and we were only pruning widgets from orders that
more than 100, not orders from customers with more than 100 widgets over
all their orders. Grant?
Cheers,
Carl Reynolds Ph: +64-9-4154790
CJN Technologies Ltd. Fax: +64-9-4154791
[EMAIL PROTECTED] DDI: +64-9-4154795
PO Box 302-278, North Harbour, Auckland, New Zealand
12 Piermark Drive, North Harbour Estate, Auckland, NZ
Visit our website at http://www.cjntech.co.nz/
> -----Original Message-----
> From: Aaron Scott-Boddendijk [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 12, 1999 3:45 PM
> To: Multiple recipients of list database
> Subject: Re: [DUG-DB]: Common SQL Query
>
>
> >> The psuedo would do something like:
> >> for each Customer in the Customer table do
> >> select count(widgets)
> >> from orders
> >> if count_widgets > 100 then
> >> Delete_widgets from orders where (some_field > 100_value)
>
> >> I guess I can do the first part with a parameterised query...
>
> >Query1:
> >select * from customers
>
> >Query2:
> >select orders.order_no, count(*) widget_count
> >from widgets, orders
> >where orders.order_no = widgets.order_no
> >and orders.customer_no = :customer_no
> >group by order_no
> >having widget_count > 100
>
> >Query2.DataSource := DataSource1; // DataSource1 has Query1 as its
> >DataSet
> >Query1.Open;
> >while not Query1.EOF do begin
> >// Can't remember whether you need to open and close Query2
> >explicitly
> >while not Query2.EOF do begin
> >// Delete 100 - x widgets from this order - for example
> >DeleteSomeWidgets( Query2order_no.AsInteger,
> > 100 - Query2widget_count.AsInteger );
> >Query2.Next;
> >end;
> >Query1.Next;
> >end;
>
> DELETE FROM
> ORDERS A
> WHERE
> 100<(
> SELECT
> COUNT(*)
> FROM
> ORDERS O
> WHERE
> O.CUST=A.CUST
> GROUP BY O.CUST
> )
>
> works doesn't it...
>
> IE Find the counts of records in the orders table for each customer
> and delete those orders for customers with more than 100 records.
>
> --
> Aaron Scott-Boddendijk
> Jump Productions
> (07) 838-3371 Voice
> (07) 838-3372 Fax
>
>
> ----------------------------------------------------------------------
> -----
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
application/ms-tnef
