Getting required records from table

Databases
1

A MySQL table is having duplicated records as under

id, activity, date, ref
1, email, 2018-01-01, ref-0100
2, call, 2018-01-01,ref-0101
3, email, 2018-01-01,ref-0101
4, sms, 2018-01-01,ref-0100
5, email, 2018-01-01,ref-0101
6, call, 2018-01-01,ref-0102
7, sms, 2018-01-01,ref-0102
8, sms, 2018-01-01,ref-0100

I want to select ref having activity != email so in above case it should return ref-0102 as this ref has no email activity in the entire table.

2

SELECT this.id , this.activity , this.date , this.ref FROM ( SELECT ref FROM table GROUP BY ref WHERE activity = 'email' HAVING COUNT(*) = 0 ) AS these INNER JOIN table AS this ON this.ref = these.ref

1 Like
3

Excellent many thanks

4

It seems there is an error on WHERE activity = ‘email’ in MySQL. I am not sure if I can use both Where and Having together?

5

There is an error in the answer of r937.

The where clause is misplaced, it should come directly after the from clause, i.e.

SELECT this.id
     , this.activity
     , this.date
     , this.ref
  FROM ( SELECT ref
           FROM table
          WHERE activity = 'email'
         GROUP
             BY ref
         HAVING COUNT(*) = 0   
       ) AS these  
INNER
  JOIN table AS this
    ON this.ref = these.ref
6

yup, i messed up, sorry

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.