如何获取与MySQL数据库的多列值匹配的值

Question

I need to match the multiple same column value of MySQL table using PHP and MySQL. I am explaining my table below.

db_user:

id      status    book_id

 1        0         22

 2        0         22

 3        1         22

 4        0         23

Here I need the select query and condition is if status=0 for same book_id means if table has lets say book_id=22 and all status=0 then only it will return value true otherwise false. I am writing one example below.

$sql=mysqli_query($connect,"select * from db_user where status=0 and....");
if(mysqli_num_rows($sql) > 0){
   $row=mysqli_fetch_array($sql);
   $data=$row['book_id'];
}else{
   return 0;
}

Here as per the example table only last row will fetch because for book_id=22 there is status=1 present. The data will only fetch when for one book_id all status=0.

Answer 1

One option uses aggregation to check the status values:

SELECT book_id
FROM db_user
GROUP BY book_id
HAVING SUM(CASE WHEN status <> 0 THEN 1 ELSE 0 END) = 0;

We can also use EXISTS:

SELECT DISTINCT t1.book_id
FROM db_user t1
WHERE NOT EXISTS (SELECT 1 FROM db_user t2
                  WHERE t1.book_id = t2.book_id AND t2.status <> 0);