Is this possible with SQL?, (I think yes, but....) 
Author Message
 Is this possible with SQL?, (I think yes, but....)

Hello, what command is needed ,(if exists) for select and delete all the
rows in one table with  Master/Detail structure and, only delete in the
detail table all the rows grouped by certain criteria that its sum(VALUE) is
zero.

That is:

ORDERS_MASTER
COMPANY ORDER  ORDER_DATE
BCN  0001   01-01-2001
BCN  0002   01-02-2001
MAD  0001   03-02-2001

ORDERS_DETAIL
COMPANY ORDER   PRODUCT VALUE
BCN  0001   01774   1000
BCN  0001   00143     300
BCN  0002   00715     200
MAD  0001   00015     -15
MAD  0001   00001       15

For select these I would use:

SELECT COMPANY,ORDER
FROM ORDERS_DETAIL
GROUP BY COMPANY,ORDER
HAVING SUM(VALUE)=0

Thanks.



Sat, 15 Nov 2003 14:47:56 GMT
 Is this possible with SQL?, (I think yes, but....)

There are probably 1,000 ways to do this. Here is one.

 In general if PK is the Primary Key (looks like yours is Company + Order)
then
DELETE from MASTER where
 PK in ( SELECT PK  FROM DETAIL Group By PK having <your criteria goes
here> )

followed within the same transaction with

delete from Detail where PK in (SELECT PK  FROM DETAIL Group By PK having
<your criteria goes here> )


Quote:
> Hello, what command is needed ,(if exists) for select and delete all the
> rows in one table with  Master/Detail structure and, only delete in the
> detail table all the rows grouped by certain criteria that its sum(VALUE)
is
> zero.

> That is:

> ORDERS_MASTER
> COMPANY ORDER  ORDER_DATE
> BCN  0001   01-01-2001
> BCN  0002   01-02-2001
> MAD  0001   03-02-2001

> ORDERS_DETAIL
> COMPANY ORDER   PRODUCT VALUE
> BCN  0001   01774   1000
> BCN  0001   00143     300
> BCN  0002   00715     200
> MAD  0001   00015     -15
> MAD  0001   00001       15

> For select these I would use:

> SELECT COMPANY,ORDER
> FROM ORDERS_DETAIL
> GROUP BY COMPANY,ORDER
> HAVING SUM(VALUE)=0

> Thanks.



Sat, 15 Nov 2003 22:35:59 GMT
 Is this possible with SQL?, (I think yes, but....)
Thanks, Dennis, but, (an always exists but), my problem is i have not a
single PK, i have a PK composed by two fields. COMPANY and ORDER and the
select returns two fields, not possible to filter with where, i believe.
Quote:
> delete from Detail where PK in (SELECT PK  FROM DETAIL Group By PK having
> <your criteria goes here> )
> > ORDERS_DETAIL
> > COMPANY ORDER   PRODUCT VALUE
> > BCN  0001   01774   1000
> > BCN  0001   00143     300
> > BCN  0002   00715     200
> > MAD  0001   00015     -15
> > MAD  0001   00001       15

> > For select these I would use:

> > SELECT COMPANY,ORDER
> > FROM ORDERS_DETAIL
> > GROUP BY COMPANY,ORDER
> > HAVING SUM(VALUE)=0

> > Thanks.



Sat, 15 Nov 2003 23:19:47 GMT
 Is this possible with SQL?, (I think yes, but....)

Use EXISTS. See SQL Books online for more detail.

Quote:
-----Original Message-----

Thanks, Dennis, but, (an always exists but), my problem is i have not a
single PK, i have a PK composed by two fields. COMPANY and ORDER and the
select returns two fields, not possible to filter with where, i believe.

> delete from Detail where PK in (SELECT PK  FROM DETAIL Group By PK having
> <your criteria goes here> )

> > ORDERS_DETAIL
> > COMPANY ORDER   PRODUCT VALUE
> > BCN  0001   01774   1000
> > BCN  0001   00143     300
> > BCN  0002   00715     200
> > MAD  0001   00015     -15
> > MAD  0001   00001       15

> > For select these I would use:

> > SELECT COMPANY,ORDER
> > FROM ORDERS_DETAIL
> > GROUP BY COMPANY,ORDER
> > HAVING SUM(VALUE)=0

> > Thanks.

.



Sun, 16 Nov 2003 02:31:48 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. HOWTO: Change progammatically an IDENTITY flag to YES or NO (with SQL if possible)

2. Yes, I am in trouble...

3. I'm a newbie, yes I am!

4. I think I am doing something wrong

5. handling data changes _ am getting stressed thinking about it

6. i think i am dreaming

7. I think I am in trouble :)

8. Is this Possible Using DTS?Could you say the details.I am new

9. Is this possible, or am I pushing it?

10. Thinking about WinHelp Tools? Think $$$

11. Thinking about WinHelp Tools? Think $$$

12. Is it possible in VB 5 ? I do not think so, What do you think?


 
Powered by phpBB® Forum Software