MySQL subselect needed-CodePudding

I just can't figure this out. I need a select statement that will find all job_id's where its profile_sent column is ALL non-zero. So in this case the select should return just "2064056592" because all its rows for profile_sent are non-zero, but 4064056590 still has a 0 in one of its rows so it is not found.

I can obviously get the distinct job_id with:

mysql> Select distinct job_id from Table;
 ------------ 
| job_id     |
 ------------ 
| 4064056590 |
| 2064056592 |
 ------------

But have no idea how to subselect where each job_id has all its profile_sent column as non-zeros.

See https://snipboard.io/x4UNKc.jpg for the table structure.

CodePudding user response：

Using a subquery to find all the distinct job_ids with profile_sent as 0 and filtering them out should work:

SELECT 
 DISTINCT t.`job_id` 
FROM
  test_table t 
WHERE t.`job_id` NOT IN 
  (SELECT DISTINCT 
    t1.job_id 
  FROM
    test_table t1 
  WHERE t1.`profile_sent` = 0)

Another approach could be grouping them by their job_id and then checking the sum of profile_sent value is 0;

SELECT t.`job_id` FROM `test_table` t 
GROUP BY t.`job_id`
HAVING SUM(t.`profile_sent` = 0)=0