Select cashiers who sold tickets for different events in the same day-CodePudding

I have next table :

CREATE TABLE sales
( id            INT    ( 10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name    VARCHAR( 99),
  cashier_name  VARCHAR( 99),
  sale_date     VARCHAR( 99),
  sale_time     VARCHAR( 99)
) ENGINE=INNODB

And next data (query included for someone to test) :

ID  EVENT_NAME        CASHIER_NAME  SALE_DATE    SALE_TIME
----------------------------------------------------------
1   Metallico 8pm     Jenny         2022-03-11   15:37
2   Metallico 8pm     Sandy         2022-03-11   16:01
3   Red Puppets 10am  Jenny         2022-03-08   09:40
4   Red Puppets 10am  Jenny         2022-03-11   08:55
5   Wild Cats 8pm     Sandy         2022-02-25   19:13
6   Wild Cats 8pm     Jenny         2022-02-25   16:59

INSERT INTO sales (id, event_name, cashier_name, sale_date, sale_time)
           VALUES (1, 'Metallico 8pm',   'Jenny', '2022-03-11', '15:37'),
                  (2, 'Metallico 8pm',   'Sandy', '2022-03-11', '16:01'),
                  (3, 'Red Puppets 10am','Jenny', '2022-03-08', '09:40'),
                  (4, 'Red Puppets 10am','Jenny', '2022-03-11', '08:55'),
                  (5, 'Wild Cats 8pm',   'Sandy', '2022-02-25', '19:13'),
                  (6, 'Wild Cats 8pm',   'Jenny', '2022-02-25', '16:59')

As the title says, I need to get JENNY because she has sales for different events in the same day (1,4), Sandy also has sales for different events but dates are different (2,5).

Next query gives me the events (3 records) but I'm not sure how to include the condition with sales in the same day :

  SELECT *
    FROM sales
GROUP BY event_name
  HAVING COUNT(*) > 1

If I change the GROUP BY I get Jenny and Sandy (2 records) but not sure how to exclude Sandy :

  SELECT *
    FROM sales
GROUP BY sale_date
  HAVING COUNT(*) > 1

(And no, this is not homework, I work at a museum with a small theater inside for children plays, concerts and contests, we are having "issues" with cashiers and money and we are trying to cross some data to catch the problem.)

CodePudding user response：

Try:

select CASHIER_NAME
from sales
group by cashier_name,sale_date
having COUNT( distinct event_name) >1 ;

Demo

All what you need is group by cashier_name,sale_date and having COUNT( distinct event_name) >1 ;

CodePudding user response：

One other option is to correlate using exists

select distinct cashier_name 
from sales s
where exists (
  select * from sales s2 
  where s2.cashier_name = s.cashier_name 
    and s2.sale_date = s.sale_date 
    and s2.event_name != s.event_name
);