subquery - Chaining MySQL with subqueries for deletion -

we have ordering system we're launch, however, need clear out test data 1 of clients. gateway portion housing multiple clients due way needed setup (ie., gateway services several different website installations talk it).

in doing this, there many tables need traverse through , remove data for, orders it's tripping me up. have client_id tables, not all...hence issue.

looking select * orders client_id = 1001. then, each of those, need select * order_authorizations orders.order_id = order_authorizations.order_id.

what i'm working far:

select * order_authorizations              order_id in (             select order_id orders                      order_authorizations.order_id = orders.order_id ,              orders.client_id = 1001     ) ; 

i'm no genius subqueries, , maybe i'm doing incorrectly, i've been through number of other threads , websites morning , no matter what, keep getting "zero results" returned...so something's not right in approach. ideas?

sample queries w/ results:

select order_id, client_id `orders` client_id = 1001 limit 0, 30 ;  rows: 10  order_id    client_id 1237    1001 1236    1001 1235    1001 1234    1001 1233    1001 1232    1001 1231    1001 1230    1001 1229    1001 1228    1001 

---

select order_id  `order_authorizations`  order_id =1237 limit 0 , 30  mysql returned empty result set (i.e. 0 rows). ( query took 0.0001 sec ) 

---

the thing is, can see there's value in order_id column matches 1237. see visually in phpmyadmin, query won't match apparently our tests.

schema details:

create table if not exists `orders` (   `order_id` int(11) not null auto_increment,   `client_id` int(11) not null,   `gateway_id` int(11) not null,   `customer_id` int(11) default '0',   `subscription_id` int(11) not null,   `card_last_four` varchar(4) not null,   `amount` varchar(11) not null,   `coupon_id` int(11) default null,   `customer_ip_address` varchar(14) default null,   `status` tinyint(1) not null default '0',   `timestamp` datetime not null,   `refunded` tinyint(3) not null,   `refund_date` datetime default null,   primary key (`order_id`) ) engine=myisam  default charset=utf8 auto_increment=1238 ; 

---

create table if not exists `order_authorizations` (   `order_authorization_id` int(11) not null auto_increment,   `order_id` varchar(200) not null,   `tran_id` varchar(255) not null,   `authorization_code` varchar(200) not null,   `security_key` varchar(200) not null,   primary key (`order_authorization_id`) ) engine=myisam  default charset=utf8 auto_increment=16 ; 

you can further convert join,

select  distinct a.*    order_authorizations         inner join orders b             on a.order_id = b.order_id    b.client_id = 1001 

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins

---

here's delete statement,

delete     order_authorizations         inner join orders b             on a.order_id = b.order_id    b.client_id = 1001