It is possible do a MYSQL SELECT query to skip some specific value

Question

Did any possible way to select from table which duplicate value that have specified value, and skip the another one?

I want to select all the record in the table based on below, but only when the same VALUE have different USER and it was not equal to 0, then skip the specific VALUE which USER equal to 0, and take the one not equal to 0.

Example Table Data:

|----|------------------|--------|
| ID | VALUE            | USER   |
|----|------------------|--------|
| 1  | HELLO WORLD      | 0      | <--- Skip This
|----|------------------|--------|
| 2  | HELLO WORLD 2    | 0      | <--- Take This
|----|------------------|--------|
| 3  | HELLO WORLD      | 5      | <--- Take This
|----|------------------|--------|
| 4  | WELCOME MY WORLD | 0      | <--- Skip This
|----|------------------|--------|
| 5  | WELCOME MY WORLD | 5      | <--- Take This
|----|------------------|--------|

Now I am using SELECT * FROM TABLE_NAME WHERE (USER = '5' OR USER = '0'); Then using PHP to filter the VALUE like

$newData = array();
foreach($data as $key => $val){
      if($val['USER'] == 5){
            $newData[] = $val;
            unset($data[$key]);
      }
      continue;
}

foreach($data as $key => $val){
      if(in_array($val['VALUE'], array_column($newData, "VALUE"))) continue;
      $newData[] = $val;
}

But using this way will lead some problem on pagination with limit

Answer 1

In SQL, you can use not exists for this. I think the logic you want is:

select t.*
from mytable t
where 
    user = 5 
    or (
        user = 0 
        and not exists (select 1 from mytable t1 where t1.value = t.value and t1.user = 5)
    )

A correlated subquery might be a simpler solution:

select t.*
from mytable t
where user = (
    select max(t1.user)
    from mytable t1
    where t1.value = t.value and t1.user in (0, 5)
)

In MySQL 8.0, you can also use window functions:

select *
from (
    select t.*, row_number() over(partition by value order by user desc) rn
    from mytable
    where user in (0, 5)
) t
where rn = 1