Combining the result of an inner join with a left join

Question

SELECT name,trans FROM skyplan_deploy.deploy_sids d WHERE apt='KBOS' AND name != trans LEFT JOIN (SELECT distinct c.sid_ident as name,c.fix_ident from corept.std_sid_leg as c INNER JOIN (SELECT sid_ident,transition_ident,max(sequence_num) seq,route_type FROM corept.std_sid_leg WHERE data_supplier='J' AND airport_ident='KBOS' GROUP BY sid_ident,transition_ident)b ON c.sequence_num=b.seq and c.sid_ident=b.sid_ident and c.transition_ident=b.transition_ident WHERE c.data_supplier='J' and c.airport_ident='KBOS')right_tbl ON d.name=right_tbl.sid_ident; 

This is my code .. when I run, I get an error in the left join saying that the syntax is incorrect. Anyone help please ... I went through the syntax tutorials, but ended up empty-handed. Thanks.

Answer 1

Move the WHERE to the end of the query. Also, what is the trans column in the WHERE ? Where is it from? If it is a string literal, put it in quotation marks.

It should be written as follows:

 SELECT name, trans FROM skyplan_deploy.deploy_sids d LEFT JOIN ( SELECT distinct c.sid_ident as name, c.fix_ident from corept.std_sid_leg as c INNER JOIN ( SELECT sid_ident, transition_ident, max(sequence_num) seq, route_type FROM corept.std_sid_leg WHERE data_supplier='J' AND airport_ident='KBOS' GROUP BY sid_ident,transition_ident ) b ON c.sequence_num=b.seq and c.sid_ident = b.sid_ident and c.transition_ident = b.transition_ident WHERE c.data_supplier='J' and c.airport_ident='KBOS' ) AS right_tbl ON d.name = right_tbl.sid_ident WHERE apt = 'KBOS' AND right_tbl.sid_ident IS NULL ;