For each two rows I have same "id"/num, and I need to get all num that their state is 0 in 2 row.
For example like in my table the result will be:
num : 2, 3
because for num 2 the state for type a is 0 and the state for type b is 0 also. But num 1 have 1 in type b so it is not good.
How I do this with SQL query or with JSON data or array in PHP? My table is:
id | num | type |state
1 | 1 | a | 0
2 | 1 | b | 1
3 | 2 | a | 0
4 | 2 | b | 0
5 | 3 | a | 0
6 | 3 | b | 0
You can give this a try:
SELECT num FROM myTable GROUP BY num HAVING sum(state) = 0
Here is the sqlfiddle.
A self join without a correlated subquery:
select t1.num from tablexxx t1 join tablexxx t2 on t2.num=t1.num and t2.state=0 and t1.state=0
Add an index on