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.
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);
You could use a not in for the id with status 1
select *
from my_table
where book_id not in (
select book_id
from my_table
where status = 1 )
Back in the day, we would have solved it this way. I say, if it ain't broke, don't fix it...
SELECT DISTINCT x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.book_id = x.book_id
AND y.status = 1
WHERE y.id IS NULL;
its very simple:
SELECT book_id,MAX(status) max_status FROM db_user GROUP BY book_id
is max_status
is 0 then all is 0 no larger than that.
You can use below query.
select * from db_user where status=0 and book_id not in (select book_id from db_user where status = 1 );
I have tested it and it works.