WHERE IN clause with multiple values

35
January 12, 2019, at 01:20 AM

I'm trying to select items from a table when any values from a list appear in an array in the table.

list_id_array is an array of ids, identifying which lists an item is included in. The integers are individual list ids that I'd like to compare against the array for each row, matching when any of the integers are included in the list_id_array.

Here's a working example with one value:

SELECT *
FROM my_table
WHERE 
1163650 IN
UNNEST(list_id_array)

This works as desired. However, I would like to check for multiple ids. For example, in addition to 1163650 I might want to also check for 654321 and 123456 and select items which includes any of those values in the list_id_array.

So, an example that does not work which might better describe what I'm looking for:

SELECT *
FROM my_table
WHERE
/*any of the below*/
(1163650, 123456, 654321) IN
UNNEST(list_id_array)

How can I do this? I did my own searching, but had trouble finding anything that wasn't referring to multiple values in the parameter after IN rather than before.

Answer 1

You have your IN logic back to front:

SELECT *
FROM my_table
WHERE 
list_id IN (1163650, 1153551, 123456)

will return any rows with a list id that matches one of those IN values.

Answer 2

Use the overlaps operator

where array[1163650, 123456, 654321] && list_id_array
READ ALSO
Best practice for storing HTML in mysql DB

Best practice for storing HTML in mysql DB

I'm working on a mini blog which allows users to add a post using a WYSIWYG editor to the site therefore I will be storing this post in my DB

27
MySql error when trying to combine two tables

MySql error when trying to combine two tables

Hi i am getting the error below when i am tryin to combine two tables, Post and User

61
Table to show the latest results from SQL

Table to show the latest results from SQL

I have a page to increase the tracking on Facebook, I created a table to display the latest results in the id column, but this table shows the oldest results for the highest resultsPlease help me edit the table to show the latest results

60