CODEWITHSUNDEEP
sqlpostgresql
raizsh
raizsh

Reputation: 486

Query multiple columns on a given value in postgresql

I have a table in my database (postgres) Table #1

----------------------------------------------------    
| name  |  id_a |  id_b | id_c | id_d |  id_e | id_f    
----------------------------------------------------

I am now given a list of ids. Some of these ids belong to id_b, some to id_c and so on till id_f. For these list of ids I need to find the corresponding id_a.

Method #1 Identify which ids belong to id_b, id_c and so on. Then use multiple in clause in my mysql-query to fetch id_a

select id_a from Table #1 t1 where t1.id_b in () or t1.id_c in ().... or t1.id_f in ();

I am looking for an alternative way.

Method #2

Is there some way to query all the columns simultaneously for a given value. Something like 

select id_a from Table #1 t1 where t1.id_b,t1.id_c,..t1.id_f in ();

Upvotes: 6

Views: 12612

Answers (3)

Mureinik
Mureinik

Reputation: 311143

In postgres, you can use the array-overlap, &&, operator:

SELECT id_a
FROM   mytable
WHERE  ARRAY[id_b, id_c, id_d, id_e, id_f] && ARRAY[value1, value2, ...]

Upvotes: 6

mateusppereira
mateusppereira

Reputation: 950

Yesss, you can do it like

SELECT columnA
FROM tableA
WHERE (columnA, columnB, columnC)::text IN ('TEST', 'TEST2');

The cast ::text may be required

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can use union 

select * from t where id_a in (

select * from 
(select id_b  from t union
select id_c from t union
select id_d from t union
select id_e from t union
select id_f from t
 ) as t1
)

Upvotes: 1

Related Questions