SELECT vh.* from table3 as vh inner join (SELECT tw.veh_id FROM table1 tw INNER JOIN (SELECT table2.inci_id FROM table2 WHERE table2.inci_id = '07000244 ' or table2.inci_id = '2007093878 ' or table2.parent_id = '07000244 ' or (LEN(table2.parent_id)>0 AND table2.parent_id = '2007093878 ')) qry1 ON tw.inci_id = qry1.inci_id WHERE canccode=' ') qry2 on vh.vehi_id = qry2.veh_idIf you want all fields form table2 try this:
SELECT * from table3 as vh inner join (SELECT tw.veh_id, qry1.* FROM table1 tw INNER JOIN (SELECT table2.* FROM table2 WHERE table2.inci_id = '07000244 ' or table2.inci_id = '2007093878 ' or table2.parent_id = '07000244 ' or (LEN(table2.parent_id)>0 AND table2.parent_id = '2007093878 ')) qry1 ON tw.inci_id = qry1.inci_id WHERE canccode=' ') qry2 on vh.vehi_id = qry2.veh_id