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 Hk, Facility Engineering Group
- MailTo:[EMAIL PROTECTED]
- http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPNGA, 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]
