CODEWITHSUNDEEP
sqlpostgresql
Antiwoutertje
Antiwoutertje

Reputation: 139

Trying to get value from other table using any in SQL

My query is:

SELECT * 
FROM table1
RIGHT JOIN table2 on table1.ID = any(table2.parents)
WHERE table1.ID = 1

My first table is:

ID   | desc         
123  | Data123
231  | Data231

My second table is:

ID   | parents
1    | {123,231}
2    | {123}

But this query isn't working. I am trying to get the data from both 123 and 231, how do I fix this?

Upvotes: 1

Views: 41

Answers (2)

user330315
user330315

Reputation:

You didn't specify what exactly you want, but if you just want to find rows in table1 where the IDs are present in table2.parents, the following should do it:

select t1.*
from table1 t1
where exists (select *
              from table2 t2
              where t1.id = any(t2.parents))
order by t1.id;

If you want information from table2 as well you need a join (and I don't think you want an outer join):

select *
from table1 t1
  join table2 t2 on t1.id = any(t2.parents)
order by t1.id

Due to the nature of a join that would return the row (123,Data123) twice - don't know if you want that or not.

Example: http://rextester.com/PUOL76353

Upvotes: 1

SlowSuperman
SlowSuperman

Reputation: 628

any(table1.parents)

parents doesn't exists in table1.

Try to change places

SELECT * 
FROM table1
RIGHT JOIN table2 on table1.ID = any(table2.parents)
WHERE table2.ID = 1

Upvotes: 1

Related Questions