[Wylug-help] OT: SQL Help

[James Holden (Wylug)]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello all...

I'm strugging to create a SQL query to tidy up the database on on online
store I manage. Homebrewed application and all that. I want to delete
some abandoned baskets from the database, but the data is stored in two
separate tables.

Table 'orders' contains the shopping baskets with information such as
the status and creation date, with primary key 'order_id'.
Table 'products' contains product information, with primary key
'product_id'.
Table 'items' links the products into the baskets along with quantity
information etc... and has primary key 'item_id'.

So, if order '603' had a six of a single item from products in it, say
product_id 71, then the items table would contain a row like:

item_id order_id qty product_id
nnn     603      6   71

Standard many-many relationship with an intermediate table.

So, I want to delete rows from 'orders' according to certain criteria
such as date and status, but also delete rows from the items table which
have the same order_id. If I were not to delete from 'items' also, it
would leave lots of orphaned items.

Can anyone help with the query to do this?

Could I do two queries, one to delete the dud lines from orders, and one
to delete the orphaned lines in 'items'?

Thanks,


James
- --
James Andrew Holden, Leeds, UK    (james at jamesholden dot net)
GPG Key: 1024D/8358863A    *Please encrypt mail where possible!*
Fingerprint:  32C9 A76F 3CFE A06C 1B00  5AAB 9877 4742 8358 863A
jamesholden.net         Buy Linux CDs here: http://fastdiscs.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/2yQ4mHdHQoNYhjoRAh4lAJ43HNw2uZTmr1W5XZLEzWNIyjQqzgCghL2J
PcA6q15gFQ2KYonb3Ny00Yg=
=oxHG
-----END PGP SIGNATURE-----