MySQL How to select multiple rows with WHERE multiple columns on the same table?

Question

I want to select room_id based on column user_id and del

room_id	user_id	del
1	23	0
1	45	0
1	56	1
25	23	0
25	45	0
25	56	0

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.

Salman Arshad · Accepted Answer · 2021-09-24 11:03:55Z

2

Use a where clause to filter matching rows, then group the results and add a having clause:

SELECT room_id
FROM t
WHERE (user_id = 23 AND del = 0)
OR    (user_id = 45 AND del = 0)
OR    (user_id = 56 AND del = 0)
GROUP BY room_id
HAVING COUNT(*) = 3 -- there are three rows with matching condition for that room

answered Sep 24, 2021 at 11:03

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aflah Najeeb · Accepted Answer · 2021-09-24 10:06:05Z

0

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();

answered Sep 24, 2021 at 10:06

Aflah Najeeb

4375 silver badges25 bronze badges

1 Comment

Charas Over a year ago

Hi, thanks, but this doesn't work, have tried it

mark_b · Accepted Answer · 2021-09-24 10:22:06Z

0

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;

answered Sep 24, 2021 at 10:22

mark_b

1,49312 silver badges19 bronze badges

Comments

Jaguar Nation · Accepted Answer · 2021-09-24 10:32:10Z

0

"user_id = 23 AND del = 0" will add the Room ID 1 to the result set as well. Add a condition for room_id = 25 in the where clause.

answered Sep 24, 2021 at 10:32

Jaguar Nation

13212 bronze badges

Comments

sukalogika · Accepted Answer · 2021-09-24 11:04:19Z

0

SELECT room_id
FROM
(
SELECT `room_id`,`user_id`,`del`,GROUP_CONCAT(user_id ORDER BY user_id ASC) ordered_user
FROM    `my_table`
GROUP BY room_id,del
HAVING ordered_user = "23,45,56" AND del=0
)
AS dbx

Explaination:

Your Data:

Group by Room_id and Del After that filter by HAVING in ordered_user

Make New dummy for only return 25

answered Sep 24, 2021 at 11:04

sukalogika

5994 gold badges11 silver badges18 bronze badges

Comments

Ergest Basha · Accepted Answer · 2021-09-24 12:41:49Z

0

An easier approach would be:

SELECT room_id
FROM table_tbl
WHERE user_id  in (23,45,56) AND del = 0 
GROUP BY room_id
HAVING COUNT(*) = 3 ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/60

answered Sep 24, 2021 at 12:41

Ergest Basha

9,3064 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL How to select multiple rows with WHERE multiple columns on the same table?

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related