slow query help 1

[thompom]

Hi,

the query below counts records where typeid = 1 and where a record with a typeid = 2 does not exist - but runs slow

can anyone see a way to improve this?

SELECT event.eventid, 
event.deptlinkid, 
cus.custitle, 
cus.firstname, 
cus.surname, 
TA.actiondate 
FROM event 
INNER JOIN cus ON cus.cusid = event.cusid 

LEFT JOIN 
(SELECT action.actiondate, 
action.eventid 
FROM action 
WHERE action.typeid = '1' 
AND action.eventid NOT IN 

(SELECT action.eventid 
FROM action 
WHERE action.typeid = '2') 

)AS TA ON event.eventid = TA.eventid 

WHERE event.userid = '39' 
AND month(TA.actiondate) = '1' 
AND year(TA.actiondate) = '2010' 
GROUP BY event.eventid 
ORDER BY TA.actiondate DESC

 

[DonQuichote]

Can you tell us what you are trying to accomplish? that may help us understand the query better.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[thompom]

Hi,

apologies the query doesnt count! - it should show records from table event where a record in table action with a typeid of '1' exists BUT a record in table action with a typeid of '2' does not exist.
table action is linked to table event by action.eventid

hope this makes sense - have simplified the query below

SELECT event.eventid
FROM event 

LEFT JOIN 
(SELECT action.eventid 
FROM action 
WHERE action.typeid = '1' 
AND action.eventid NOT IN 

(SELECT action.eventid 
FROM action 
WHERE action.typeid = '2') 

)AS TA ON event.eventid = TA.eventid 

GROUP BY event.eventid

 

[r937]

SELECT event.eventid
  FROM event 
LEFT OUTER
  JOIN ( SELECT eventid 
           FROM action 
         GROUP
             BY eventid
         HAVING COUNT(CASE WHEN typeid = '1'
                           THEN 'count me' END) > 0
            AND COUNT(CASE WHEN typeid = '2'
                           THEN 'no, me!!' END) = 0
       ) AS TA 
    ON TA.eventid = event.eventid

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

thanks (again) rudy ;-)

 

[r937]

except i think it should have been INNER JOIN instead of LEFT OUTER JOIN !!

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon