I want to select room_id based on column user_id and del

This is an example of my table, and I want to select room_id WHERE:

"user_id = 23 AND del = 0" (del of the row where user_id=23)

AND

"user_id = 45 AND del = 0" (del of the row where user_id=45)

AND

"user_id = 56 AND del = 0" (del of the row where user_id=56)

The query should only return room_id = 25 How do I achieve this with PHP or CodeIgniter?

I have tried using this and it's not working:

$this->db->select()
->from('table')
->where(['user_id' => 23, 'del' => 0])
->where(['user_id' => 45, 'del' => 0])
->where(['user_id' => 56, 'del' => 0])
->get()->result_array();

*Notes: it worked when I tried using join tables on the same table, but when I have 10 user_id (joining the same table 10 times), the query runs super slow (because it is a big table with almost close to a million rows).

Thank you in advance.

CodePudding user response：

You can try like this

SELECT room_id
FROM table
WHERE user_id = 23 AND del = 0 AND user_id = 45 AND del = 0 AND user_id = 56 AND del = 0;

Then the query may look like this

$this->db->select('room_id')
->from('table')
->where(['user_id' => 23, 'del' => 0])
->orWhere(['user_id' => 45, 'del' => 0])
->orWhere(['user_id' => 56, 'del' => 0])
->get()->result_array();

CodePudding user response：

You can either put all your user_ids in parentheses:

SELECT room_id
FROM table
WHERE (user_id = 23 OR user_id = 45 OR user_id = 56) AND del = 0;

or you can user the WHERE IN clause:

SELECT room_id
FROM table
WHERE user_id IN (23, 45, 56) AND del = 0;