sql - MySQL joining tables gives unexpected output -

i have been bashing head against brick wall trying working , don't know why it's not.

i join tables , b using my_field. run sub query my_field table b where complete = 1. want use query tables c , d

this current query

select      table_a.*,     table_b.*,     table_c.*,     table_d.* table_a     inner join table_b on         table_a.my_field = table_b.my_field     left join (select my_field table_b complete ='1') test on         table_b.my_field = test.my_field     right join table_c on         test.my_field = table_c.my_field     inner join table_d on         table_c.my_field = table_d.my_field 

this output of current query

table_a.field1  |   table_a.field2  |   table_b.field1  |   table_b.field2  |   table_c.field1  |   table_c.field2  |   table_d.field1  |   table_d.field2  |   test.complete =============================================================================================================================================================================       |         |         |         |         |         |         |         |       1     null        |       null        |       null        |       null        |         |         |         |         |       0 

and want get

table_a.field1  |   table_a.field2  |   table_b.field1  |   table_b.field2  |   table_c.field1  |   table_c.field2  |   table_d.field1  |   table_d.field2  |   test.complete =============================================================================================================================================================================       |         |         |         |         |         |         |         |       1       |         |         |         |   null            |   null            |   null            |       null        |       0 

update:

here table structures. i've removed columns have not link other table

create table if not exists `table_a` (   `id` int(10) not null auto_increment,   `code` varchar(255) not null,   primary key (`id`),   unique key `code` (`code`) );  create table if not exists `table_b` (   `id` int(10) not null auto_increment,   `code` varchar(255) not null,   `complete` enum('0','1') not null default '0',   primary key (`id`) );  create table if not exists `table_c` (   `id` int(10) not null auto_increment,   `code` varchar(255) not null,   primary key (`id`),   unique key `code` (`code`) );  create table if not exists `table_d` (   `id` int(10) not null auto_increment,   `code` varchar(255) not null,   primary key (`id`) ); 

why don't rid of left join? makes easier read.

select    table_a.*,   table_b.*,   table_c.*,   table_d.* table_a   inner join table_b on     table_a.my_field = table_b.my_field   left join table_c on     table_b.my_field = table_c.my_field , table_b.complete ='1'   left join table_d on     table_c.my_field = table_d.my_field