Delete from multiple tables

[maxat]

Hi, I am new in mysql and having problem with deleting rows from multiple tables.

I have 3 tables

1. table users
us_id <- primary key

2. table items

  od_id <- primary key
  pd_id <- primary key
  us_id <- primary key
  it_qty

3.table orders

od_id <- primary key


In table "items" all are foreign keys from another tables except it_qty.
The problem is when I delete user from table "user", I want delete all his records from "items" and "order" tables. My tables are in MyIsam. so far I couldn't get any valid query.
Please help me with this query. Thanks in advance.

[designationlocutus]

Are you linking all of your tables together within the DELETE query?

Please post your query contruct.

[maxat]

DELETE FROM users, items, orders WHERE users.us_id = items.us_id AND items.od_id = orders.od_id AND users.us_id = '$usrID'

is there anything wrong?

[shoz]

Try the following
[code]
DELETE
u,i,o
FROM
users AS u
LEFT JOIN
items AS i
ON u.us_id=i.us_id
LEFT JOIN
orders AS o
ON i.od_id=o.od_id
WHERE u.us_id = $usrID
[/code]

[url=http://dev.mysql.com/doc/refman/4.1/en/delete.html]Delete Syntax[/url]

[maxat]

Thanks shoz for reply.

I have tried to run the query, but getting an error " Not unique table/alias: 'u' " Do I have to change my column names?

[shoz]

MYSQL 4.0 and MYSQL 4.1 or higher deal with aliases in multi-table deletes differently. To avoid problems try this query which doesn't use aliases
[code]
DELETE
users,items,orders
FROM
users
LEFT JOIN
items
ON users.us_id=items.us_id
LEFT JOIN
orders
ON items.od_id=orders.od_id
WHERE users.us_id = $usrID
[/code]

[maxat]

Thank you shoz very much. It is working now.  I realy appreciate your help.