Question 327404 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL – Select values from one field that does not contain 0 value in an other field

Do you know how can i get values from Field 1 that does not contain 0 value in Field 2.
For example I would like to select only "BBB" and "DDD" from the table below :

Field 1 Field 2
AAA 1
AAA 2
AAA 0
BBB 1
BBB 2
CCC 0
CCC 2
DDD 3

(BBB and DDD don’t contain 0 values in Field2)

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Great! Thank you all for your immediate responses!


  2. 0

    This can be done using group by and having clauses, the condition is that the count of rows where Field2 equals 0 must be zero :

    select Field1
from mytable
group by Field1
having count(case when Field2 = 0 then 1 end) = 0;

    Demo here

    Login or Signup to reply.
  3. 0

    I can think of two ways in which it could be achieved:

    Using Subquery:

    SELECT DISTINCT Field1
FROM your_table
WHERE Field1 NOT IN (
    SELECT Field1
    FROM your_table
    WHERE Field2 = 0
);

    Using group by and having

    SELECT Field1
FROM your_table
GROUP BY Field1
HAVING SUM(CASE WHEN Field2 = 0 THEN 1 ELSE 0 END) = 0;

    sample run

    Login or Signup to reply.
  4. 0

    I would prefer NOT EXISTS

    select m.* 
from my_table m
where not exists (select 1 from my_table mt  where m.field1 = mt.field1 and field2 = 0);

    Or with having clause

    select field1 
from my_table
group by field1
having min(field2) <> 0;

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search