Intersection of two select

Question

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.

Answer 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)