CODEWITHSUNDEEP
mysql
user2037445
user2037445

Reputation: 161

Conversion of not exists to another query that uses join

Here is the query using not exists.. Can anyone rewrite this query using join? 

SELECT name,
       trans
FROM skyplan_deploy.deploy_stars d
WHERE apt='KOPF'
  AND name!=trans
  AND NOT EXISTS
    (SELECT DISTINCT c.star_ident,
                     c.fix_ident
     FROM corept.std_star_leg 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
     WHERE c.data_supplier='J'
       AND c.airport_ident='KOPF'
       AND d.name=c.star_ident
       AND d.trans=c.fix_ident);

Thank you

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                 GJI  
KOPF            A                 FJI  
KOPF            A                 DHI  
KOPF            B                 KDI  
KOPF            B                 VNM

I need to output 

A  FJI  
A  DHI  
B  VNM

Upvotes: 0

Views: 60

Answers (1)

Ken Clark
Ken Clark

Reputation: 2530

Try this:

SELECT NAME, trans
FROM skyplan_deploy.deploy_stars d
Left Join
(
        SELECT DISTINCT c.star_ident, c.fix_ident
        FROM corept.std_star_leg 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
        WHERE c.data_supplier = 'J' AND c.airport_ident = 'KOPF' 
)tbl
On d.NAME = tbl.star_ident AND d.trans = tbl.fix_ident
Where tbl.star_ident Is Null

Upvotes: 2

Related Questions