CODEWITHSUNDEEP
mysqlsql
user3223205
user3223205

Reputation:

SELECT rows where col1 is value and another row where col1 is not value

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

i am running this query:

SELECT COUNT(DISTINCT callid) as r FROM voipnow.ast_queue_log 
WHERE queuename = '0536*401' 
AND time > '2014-03-19 15:38:00' 
AND callid NOT IN (SELECT callid FROM voipnow.ast_queue_log 
                   WHERE event = 'CONNECT' 
                   OR event = 'ABANDON') 
AND event = 'RINGNOANSWER'

which shows all rows where the CALLID column is the same but where there is no row that has the event CONNECT or ABANDON or RINGNOANSWER

so when the caller is waiting there are only ever rows with events ENTERQUEUE or RINGNOANSWER

this is working fine, however when the first row is inserted (with event ENTERQUEUE) it should be displaying a count of 1 but it shows 0 until another row is inserted with event RINGNOANSWER

Upvotes: 0

Views: 404

Answers (1)

Barmar
Barmar

Reputation: 781340

Try this:

SELECT COUNT(DISTINCT callid) as r 
FROM voipnow.ast_queue_log 
WHERE queuename = '0536*401' 
AND time > '2014-03-19 15:38:00' 
AND callid NOT IN (SELECT callid FROM voipnow.ast_queue_log 
                   WHERE event IN ('CONNECT', 'ABANDON', 'RINGNOANSWER'))

DEMO

Upvotes: 1

Related Questions