CODEWITHSUNDEEP
sqlpostgresql-9.1informix
chebus
chebus

Reputation: 772

Need suggestions for writing an optimized SQL query

I am trying to frame an SQL in informix /Postgres.

Following are the tables,

 Job                                JobType 
---------                          --------------------   
id,type_id,user_description          id,code,description

Example data for reference,

      Job                 JobType
------------          ---------------
     1,3,'ABC'            3,'CRT','CREATE' 
     2,4,'POST1-ABC'      4,'PST1','POST1'
     3,3,'DEF'            5,'PST2','POST2' 
     4,5,'POST2-DEF' 
     5,3,'GHI'  
     6,4,'POST1-GHI'

The requirement is i need to fetch all the records in Job table where the jobs are not posted yet which can be found by looking at the description .

Note : Posted means here If the user_description in Job table is appended by POST1+'-'or POST2+'-' ,it means that job is posted. To elaborate, I create ABC and when i post it the description of the post record of ABC will be 'POST1-ABC'.

user_description in Job table != (not equal to) JobType PST1+ '-'+ user description

I have written the following query to achieve the above goal

SELECT p.* FROM  Job p 
         JOIN JobType t1 ON (p.type_id = t1.id AND t1.code = 'CRT' AND LENGTH(p.user_description) > 0 )  
         WHERE p.id NOT IN (   
         select PARENT.id  from (SELECT p.id,(select description FROM JobType  WHERE code = 'PST1')||'-'||p.user_description PST1 
         ,(select description FROM JobType  WHERE code = 'PST2')||'-'||p.user_description PST2 
     FROM Job p JOIN JobType t1 ON   (p.type_id = t1.id AND t1.code = 'CRT'  AND LENGTH(p.user_description) > 0 )
     ) PARENT ,
    (   SELECT c.id,c.user_description FROM Job c JOIN JobType t   
        ON (c.type_id = t.id AND (t.code = 'PST1' OR t.code = 'PST2') AND LENGTH(c.user_description) > 0) ORDER BY 2
    )
    CHILD 
        where  (PARENT.PST2 = CHILD.user_description or PARENT.PST1 = CHILD.user_description ) 
    )

However i will be thankful to you if you can guide to optimize this query in a better way.

EDIT :

Assuming these are existing records in the table.
            Job               
    ------------        
         1,3,'ABC'            
         2,4,'POST1-ABC'     
         3,3,'DEF'            
         4,5,'POST2-DEF' 
         5,3,'GHI'  
         6,4,'POST1-GHI'
         7,3, 'XYZ'
         8,3, 'ASD

'

 Expected OUTPUT : Records which are not posted
         ---------------------
           7,3,'XYZ' 
           8,3,'ASD'

Thanks.

Upvotes: 0

Views: 40

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

SELECT j.* 
FROM Job AS j
  JOIN JobType AS jt
    ON jt.id = j.type_id
WHERE jt.code = 'CRT'
  AND NOT EXISTS
      ( SELECT 1
        FROM Job AS p
          JOIN JobType AS pjt
            ON pjt.id = p.type_id 
        WHERE p.user_description = pjt.description || '-' || j.user_description
          AND pjt.code LIKE 'PST%'
      ) ;

Test at SQL-Fiddle

Upvotes: 1

Related Questions