Intersection of two

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 whose "is_in" is 1 at least once and who never had an "is_in" equal to 0 (in this case, one client_id 3).

To do this, I made two requests:

 SELECT client_id FROM foo WHERE is_in = 1; 

and

 SELECT client_id FROM foo WHERE is_in = 0; 

And I planned to use INTERSECT between them, so I can get general records between the two selections, so I just need to do "the number of clients with is_in = 1" - "count (the result of the intersection)".

But INTERSECT cannot be used with MYSQL, is there an INTERSECT alternative that works in this case, or an easier way to get what I need (I feel like I'm doing difficult for nothing).

Thanks.

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 only need a customer 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) 

Answer 2

that you could sum them up and get the smallest value grouped by customer ID, and as a result exclude any of those who had zero. Try the following:

 SELECT COUNT(client_id) FROM foo GROUP BY client_id HAVING SUM(is_in) > 0 && MIN(is_in) > 0