Remove matching values by comparing two database tables Codeigniter/Mysql

In my codeigniter model I have this function :

public function getUserNo()
{
    $query = $this->db->query("select userno from Users where active=1");
    return $query->result_array();
}

This code outputs the userno from the Users table.

I have another table called Userslist which also contains the userno field.

I want a query such that it first gets all the userno from the Userlist table and then check if they exist in the Users table and if they exist then return all the userno from the Users table except for those which are there in the Userslist table. How do I write this query?

Answer

You could use a sub query using the SQL NOT INenter link description here

select userno from Users where active= 1 AND userno NOT IN (SELECT userno FROM Userlist)

Advertisement

Answer