SQL query returning results based off values in an array PostgreSQL

Question

I Have two tables,

Table1
id - BIGINT
otherKey - VARCHAR(45) <---Unique

Table2
table1ID - BIGINT

The table1ID field in table2 is the same value as the id field in Table1.

I have an array containing values corresponding to the field otherKey in Table2.

myArray = {'key1', 'key2', 'key3'}

I am trying to return all Table2 rows whose otherKey value exists in myArray. I have:

SELECT * FROM Table2 WHERE (SELECT id FROM Table1 WHERE otherKey = ANY(myArray))

But I know using '=' , means the subquery can't return multiple results.

Any suggestions would be great!

Are you just looking for WHERE table1id IN (...subquery...)? — Richard Huxton
– Richard Huxton, Commented Dec 16, 2013 at 15:37
Wow yeah, I guess I am, if you want to make it an answer I'll accept it. — Super_user_one
– Super_user_one, Commented Dec 16, 2013 at 15:42

Richard Huxton · Accepted Answer · 2013-12-16 16:03:21Z

1

You are quite right, if you want the outer subquery to match multiple rows from the inner subquery then equals isn't up to the job.

Which is why they invented IN.

SELECT * FROM Table2 WHERE table1id IN (SELECT ...)

answered Dec 16, 2013 at 16:03

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

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

1 Answer 1