Re: Newbie: Rewriting A Query- How?

[Johan Hook]

Hi Ken,

you might try:
SELECT s.PROJID,s.PROJECTNAME
FROM  S_PROJECTREGISTER s
INNER JOIN SL_PROGPROJ l ON s.PROJID = l.PROJID AND l.SNAPSHOTID = 56
INNER JOIN SL_PORTPROG p ON l.PROGID = p.PROGID AND p.SNAPSHOTID = 56
        AND p.PORTID IN (100994,100996)
WHERE  s.SNAPSHOTID = 56
AND s.PROJECTSTATUS <> 'Potential'
Hope this helps,
        /Johan
Ken Brown wrote:

Anyone any idea how I could rewrite this as a join - all the indexes are in
place and each of the components of this work fine - its only when the
combined subqueries are passed to the root query does it appear to go wrong
(Takes around 1.5 mins to complete and causes 100% processor utilisation
while executing).  (May be something for the developers to look at).
 
But in the meantime I need to get this going 
 
SELECT PROJID,PROJECTNAME
FROM  S_PROJECTREGISTER
WHERE  SNAPSHOTID = 56
AND   PROJID IN ( SELECT PROJID
          FROM  SL_PROGPROJ
          WHERE  SNAPSHOTID = 56
          AND   PROGID IN ( SELECT PROGID
                    FROM  SL_PORTPROG
                    WHERE  SNAPSHOTID = 56
                     AND PORTID IN(100994,100996) ) )
AND PROJECTSTATUS <> 'Potential'
 
Ken

 <mailto:[EMAIL PROTECTED]>  



--
Johan Höök, Facility Engineering Group
                - MailTo:[EMAIL PROTECTED]
                - http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]