Sumon Sarker Sumon Sarker - 24 days ago 6
MySQL Question

How can I update this MySQL query to get proper data?

I have a table

my_table
look like -

+---------------------+---------------------+
| phone | status |
+---------------------+---------------------+
| 019 | NI |
+---------------------+---------------------+
| 019 | DROP |
+---------------------+---------------------+
| 019 | SALE |
+---------------------+---------------------+
| 018 | B |
+---------------------+---------------------+
| 016 | DROP |
+---------------------+---------------------+


My QUERY

SELECT DISTINCT(phone) AS phone FROM my_table WHERE status NOT IN ('NI','SALE','B')


RESULT

+---------------------+
| phone |
+---------------------+
| 019 |
+---------------------+
| 016 |
+---------------------+


But I want like below

+---------------------+
| phone |
+---------------------+
| 016 |
+---------------------+


I don't want phone
019
in my query result. Because phone
019
have already
NI
and
SALE
status.

How can i do it by updating my query?

Answer

I'd use an anti-join pattern:

 SELECT t.phone
   FROM my_table t
   LEFT
   JOIN my_table r
     ON r.phone = t.phone
    AND r.status IN ('NI','SALE','B')
  WHERE r.phone IS NULL
 GROUP BY t.phone

Think of it this way. We can get a list of all phone values...

And we can get a list of phone values that have a status 'NI', 'SALE' or 'B'.

The trick is returning values of phone from the "all" list excluding those that are in the second list.

The anti-join is just an option; there are other query patterns that will return an equivalent result.


Some other options are a NOT EXISTS subquery...

 SELECT t.phone
   FROM my_table t
  WHERE NOT EXISTS ( SELECT 1
                       FROM my_table r
                      WHERE r.phone = t.phone
                        AND r.status IN ('NI','SALE','B')
                   )
 GROUP BY t.phone

Another option is conditional aggregation...

 SELECT t.phone
   FROM my_table t
 GROUP BY t.phone
HAVING IFNULL(SUM(t.status IN ('NI','SALE','B')),0) = 0
Comments