CODEWITHSUNDEEP
mysqlsql
Jonathan Twite
Jonathan Twite

Reputation: 952

Selecting rows associated with other rows with specific value

I have data about phone calls in the form of events which looks something like:

|event_id|call_id|event_type      |service|...
|--------|-------|----------------|-------|---
|       1|      1|Call Started    |null   |
|       2|      1|Recorded Message|null   |
|       3|      1|Call at IVR     |null   |
|       4|      1|Agent Ringing   |Sales  |
|       5|      1|Agent Answers   |Sales  |
|       6|      2|Call Started    |null   |
|       7|      2|Recorded Message|null   |
|       8|      2|Call at IVR     |null   |
|       9|      1|Disconnected    |null   |
|      10|      1|Call Ended      |null   |
|      11|      3|Call Started    |null   |
|      12|      3|Recorded Message|null   |
|      13|      2|Agent Ringing   |Support|
|      14|      3|Agent Ringing   |Sales  |
|      15|      2|Agent Answers   |Support|
|      16|      3|Agent Answers   |Sales  |
|      17|      3|Call Hold       |null   |
|      18|      2|Disconnected    |null   |
|      19|      2|Call Ended      |null   |
|      20|      3|Call Retrieved  |Sales  |
|      21|      3|Disconnected    |null   |
|      22|      3|Call Ended      |null   |

I want to select only those events that are associated with a call to Sales. As you can see, only certain types of event include data in the service column, which is the the column that I need to filter on. Calls also are not consistent on the number of events they contain due to differences in how they are handled.

I have been filtering the calls in my application by loading all events then using a GroupBy() on call_id and finally filtering these groups by selecting any that have any events with service equalling Sales.

As in the actual data there is a huge number of calls that I do not want, it would be more efficient to filter this at the database. How would I do this?

Something like

SELECT event_id
FROM events
GROUP BY call_id
HAVING (SELECT COUNT(*) FROM ***this_group*** WHERE service='Sales') > 0

The intended output should be

|event_id|
|--------|
|       1|
|       2|
|       3|
|       4|
|       5|
|       9|
|      10|
|      11|
|      12|
|      13|
|      16|
|      17|
|      20|
|      21|
|      22|

Upvotes: 0

Views: 34

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • In a subquery, determine all the unique call_id value(s) which has atleast one row containing service = 'SALES'.
  • Just use this subquery results in the outer query to match call_id and get all event_id value(s)

Approach 1: Use a Derived Table

SELECT e1.event_id
FROM events AS e1
JOIN (SELECT DISTINCT e2.call_id
      FROM events AS e2 
      WHERE e2.service = 'SALES') AS dt 
  ON dt.call_id = e1.call_id

Approach 2: Use a WHERE .. IN(..)

SELECT e2.event_id
FROM events AS e2 
WHERE e2.call_id IN (SELECT DISTINCT e1.call_id
                     FROM events AS e1
                     WHERE e1.service = 'SALES')

Upvotes: 2

Barry Piccinni
Barry Piccinni

Reputation: 1791

OK, I think what you are looking for is something like the following. I have used the WITH query to single out the calls you're looking for, and a second query to single out events from these calls. 

WITH salesCalls AS (
  SELECT call_id, event_type, service
  FROM events
  WHERE event_type = "Agent Answers"
  AND service = "Sales"
  GROUP BY call_id, event_type, service
);

SELECT event_id
FROM events
WHERE call_id IN (SELECT call_id FROM salesCalls)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use EXISTS :

SELECT e1.event_id
FROM events e1 
WHERE EXISTS (SELECT 1 FROM events e2 WHERE e2.call_id = e1.call_id AND e2.service = 'SALES');

Upvotes: 2

Related Questions