CODEWITHSUNDEEP
phpmysqlsql
user3223205
user3223205

Reputation:

Show rows in MySQL table where another row does not exist

I have a table in my MySQL database which has columns:

I want to select all rows where the event is 'ENTERQUEUE':

SELECT count(*) AS avg_hold_time
FROM ast_queue_log
WHERE queuename = '0536*401'
  AND DATE(time) = '2014-03-07'
  AND event = 'ENTERQUEUE'
ORDER BY time DESC;

But then I want to show all the other rows where the 'callid' is equal the result from the above query (for each row returned in the above query) AND there is no row where the event equals 'CONNECT'.

Upvotes: 1

Views: 400

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53860

This is generally solved with a self-join in the form of an anti-join:

SELECT avg(aql1.arg1) AS avg_hold_time
FROM ast_queue_log aql1
// Left outer join on other records with 'CONNECT' event for same callid
LEFT JOIN ast_queue_log aql2
ON aql1.callid = aql2.callid AND aql2.event = 'CONNECT'
WHERE aql1.queuename = '0536*401'
  AND DATE(aql1.time) = '2014-03-07'
  AND aql1.event = 'ENTERQUEUE'
  // Then only include those records that didn't successfully join
  AND aql2.callid IS NULL
ORDER BY aql1.time DESC;

Maybe you need to throw some other conditions on the JOIN, such as a 'CONNECT' event with the same queuename and date, but you haven't specified.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311563

You could use a subquery with the NOT EXISTS operator:

SELECT   AVG(arg1) AS avg_hold_time 
FROM     ast_queue_log aql
WHERE    queuename = '0536*401' AND
         DATE(time) = '2014-03-07' AND
         event = 'ENTERQUEUE' AND
         NOT EXISTS (SELECT cnt.callid
                     FROM   ast_queueu_log cnt
                     WHERE  -- Same conditions as the surrounding query
                            queuename = '0536*401' AND
                            DATE(time) = '2014-03-07' AND
                            event = 'CONNECT' AND
                            -- matching callids
                            cnt.callid = aql.callid)
ORDER BY time DESC

Upvotes: 1

Related Questions