5

I have this table :

+----+-----------+-------+
| id | client_id | is_in |
+----+-----------+-------+
| 1  |     1     |   0   |
+----+-----------+-------+
| 2  |     2     |   0   |
+----+-----------+-------+
| 3  |     1     |   1   |
+----+-----------+-------+
| 4  |     2     |   1   |
+----+-----------+-------+
| 5  |     3     |   1   |
+----+-----------+-------+
| 6  |     3     |   1   |
+----+-----------+-------+
| 7  |     1     |   0   |
+----+-----------+-------+
| 8  |     4     |   0   |
+----+-----------+-------+
| 9  |     4     |   0   |
+----+-----------+-------+

And I need to get the number of clients that have 'is_in' equal to 1 at least one time and that have never had 'is_in' equal to 0 (in this case one the client_id 3).

To do so, I made two queries:

SELECT client_id FROM foo WHERE is_in = 1;

and

SELECT client_id FROM foo WHERE is_in = 0;

And I planned to to an INTERSECT between them so I can get the common entries between the two selects so I just need to do "number of clients with is_in = 1" - "count(of the result of the intersect)".

But INTERSECT can't be used with MYSQL, is there an alternative to INTERSECT that work in this case or a simpler way to get what I need (I'm feeling that I'm doing complicated for nothing).

Thank you.

hichris123
  • 10,145
  • 15
  • 56
  • 70
Gatoyu
  • 642
  • 1
  • 6
  • 22

2 Answers2

1
SELECT id, client_id FROM foo WHERE is_in = 1 AND client_id NOT IN (SELECT client_id FROM foo WHERE is_in = 0)

Or, if you need only the client number:

SELECT DISTINCT client_id FROM foo WHERE is_in = 1 AND client_id NOT IN (SELECT client_id FROM foo WHERE is_in = 0)
Gianmarco
  • 2,536
  • 25
  • 57
0

what you could so sum them up and get the smallest value, grouped by client id and have the result exclude any of those that had a zero. Try this:

SELECT COUNT(client_id)
FROM foo
GROUP BY client_id
HAVING SUM(is_in) > 0 && MIN(is_in) > 0
DBug
  • 2,502
  • 1
  • 12
  • 25