Using an array in SELECT IN query?

Question

I want to know if it is possible to create a query where the WHERE clause has one condition but the value is an array.

This is the code & image related table in the database:

//array in user.id_lokasi => (PL001,PL002,PL003)    
SELECT * FROM `lokasi` 
         WHERE `id_lokasi` IN (SELECT id_lokasi FROM user WHERE id_user='admin')

If i type manually SELECT * FROM lokasi WHERE id_lokasi IN ('PL001', 'PL002', 'PL003'); its correct, but when i select from table user there is no data show — Sigit Dwi Prasetyo
– Sigit Dwi Prasetyo, Commented Feb 20, 2018 at 8:26
You need to normalize your database, you should not have a list of keys in a single database field. — Nigel Ren
– Nigel Ren, Commented Feb 20, 2018 at 8:33

lloiacono · Accepted Answer · 2018-02-20 08:10:26Z

2

Your query above should work already. Regarding your question if you have a set of id_lokasi you want to use in the WHERE you can do this:

SELECT * FROM `lokasi` 
     WHERE `id_lokasi` IN ('PL001', 'PL002', 'PL003');

As to your original query, you could rewrite it like this to avoid the subquery:

SELECT l.* FROM `lokasi` l
LEFT JOIN user u ON
    u.id_lokasi = l.id_locasi AND u.id_user = 'admin'

answered Feb 20, 2018 at 8:10

lloiacono

5,0804 gold badges35 silver badges51 bronze badges

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

1 Comment

Sigit Dwi Prasetyo Over a year ago

when I change the parameters in u.id_user='user', all data appears... I just want to bring up the data based on array id_lokasi in user table.

Collectives™ on Stack Overflow

Using an array in SELECT IN query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related