CODEWITHSUNDEEP
sqlsql-serversql-server-2008
db-rocker
db-rocker

Reputation: 51

SQL - Return results only if 3 events exist in table

hoping to get some help with my query below.

What I need, is results returned ONLY IF all 3 event id's exist from the one table.

A Terminal ID can have multiple event ID's associated to it in this table. I'm only looking for the Terminal ID's which have 3 particular events in there within the specified date range:

The below query returns Terminal ID's that have any one of the three event id's and i'm not sure how to tell it to get what I need.

SELECT
 EJTM.terminal_id

FROM
 IG_LOG..EJ_Terminal_Master EJTM
 JOIN it_sysweb..EJ_Tran_Event_Master EJEM ON EJTM.event_type_id = EJEM.EJ_tran_event_id

WHERE
 EJEM.EJ_tran_event_id in 
(
 5011 --SignOn
,5119 --Reinit Printer
,5101 --NoSale 
 )                          
AND
 EJTM.event_date_time between '2017-06-06 03:00:00.000' and '2017-06-08 03:00:00.000'
AND
 EJEM.language_id = 'EN-US'

GROUP BY
 EJTM.terminal_id

Upvotes: 1

Views: 79

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can do GROUP BY with COUNT, e.g.:

SELECT tm.terminal_id
FROM IG_LOG..EJ_Terminal_Master.tm JOIN it_sysweb..EJ_Tran_Event_Master em
ON tm.event_type_id = em.EJ_tran_event_id
WHERE em.EJ_tran_event_id IN (5011, 5119, 5101)
AND em.event_date_time between '2017-06-06 03:00:00.000' AND '2017-06-08 03:00:00.000'
AND em.language_id = 'EN-US'
GROUP BY tm.terminal_id
HAVING COUNT(DISTINCT em.EJ_tran_event_id) = 3;

Upvotes: 1

Ven
Ven

Reputation: 2014

Although bit robust, using and clause will work 

WHERE
      EJEM.EJ_tran_event_id = '5011' --SignOn
 AND  EJEM.EJ_tran_event_id = '5119' --Reinit Printer
 AND EJEM.EJ_tran_event_id = '5101' --NoSale                        
 AND
 EJTM.event_date_time between '2017-06-06 03:00:00.000' and '2017-06-08 03:00:00.000'
 AND
 EJEM.language_id = 'EN-US'

Upvotes: 1

Related Questions