CODEWITHSUNDEEP
sqlpostgresqlselect
Super_user_one
Super_user_one

Reputation: 77

SQL query returning results based off values in an array PostgreSQL

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!

Upvotes: 2

Views: 147

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22972

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 ...)

Upvotes: 1

Related Questions