当该表中的值为空时，php选择在另一个表中找不到的用户

Question

I have a query that is supposed to look through the rota table and select the idstaff an then find all users that are not currently found in that result. As long as idstaff is not null this query works fine however there are cases where idstaff will be NULL. In this case the query fails. Is there any way I can make it work in both instances?

   $query = sprintf("SELECT user.*
    FROM user 
    WHERE user.iduser NOT IN (SELECT idstaff FROM rota WHERE idrota=%s)",
    $this->db->GetSQLValueString($idrota, "int"));
    $result = $this->db->query($this->db->link, $query) or die($this->db->error($this->db->link));

Answer 1

Taken straight from my comment, your query string should be like this:

$query = sprintf("SELECT user.*
FROM user 
WHERE user.iduser NOT IN (SELECT idstaff FROM rota WHERE idrota=%s AND idstaff <> NULL)",
$this->db->GetSQLValueString($idrota, "int"));