Stuck with left join

Question

I tried this but unable to return all the rows. It's returning only some rows from the left query. Please find the bug.

SELECT c.star_ident, c.fix_ident 
from corept.std_star_leg as c  
INNER JOIN  
(
  SELECT star_ident,
    transition_ident,
    max(sequence_num) seq,
    route_type 
  FROM     corept.std_star_leg
  WHERE data_supplier='J' 
    AND airport_ident='KOPF'  
  group by star_ident,transition_ident
) b  
  ON c.sequence_num=b.seq 
  and c.star_ident=b.star_ident 
  and c.transition_ident=b.transition_ident
LEFT OUTER JOIN  
(
  SELECT name,
    trans 
  FROM skyplan_deploy.deploy_stars d 
  WHERE apt='KOPF' 
    AND name!=trans
) as x  
  on x.name=c.star_ident 
  and x.trans=c.transition_ident  
where c.data_supplier='J' 
  and c.airport_ident='KOPF' 
  and x.name is null;  

Let corept.std_star_leg table be this.

star_ident transition_ident sequence_num fix_ident airport
A               XX               10         QWE     KOPF  
A               XX               20         WER     KOPF  
A               XX               30         HYU     KOPF  
A               XX               40         GJI     KOPF   
B               YY               10         SJI     KOPF  
B               YY               20         DJI     KOPF  
B               YY               30         FJI     KOPF  
B               YY               40         GHI     KOPF  
B               YY               50         KDI     KOPF 

After performing inner join the result will be obtained as follows.

A               XX               40         GJI  
B               YY               50         KDI  

Thus retrieving the max sequence_num rows. After that the skyplan_deploy.deploy_stars table will be as follows.

apt            name              trans    
KOPF            A                 FJI  
KOPF            A                 DHI   
KOPF            B                 VNM  

I need to output

A  GJI   
B  KDI

Answer 1

This may work.Check out once.

SELECT DISTINCT c.airport_ident,c.sid_ident,c.transition_ident,c.fix_ident
               FROM corept.std_sid_leg 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='KOPF'
                 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
                LEFT JOIN
                 (SELECT name,trans
                  FROM skyplan_deploy.deploy_sids
                  WHERE apt='KOPF'
                  AND name!=trans) d
                 ON d.name=c.sid_ident AND d.trans=c.fix_ident
                 WHERE c.data_supplier='J' AND c.airport_ident='KOPF' AND d.name is null