2

I'm looking for query that can return same results as two given queries:

select foo as res1 from table t where t.id in (1,2)
select foo as res2 from table t where t.id in (3,4)

I need something like:

select
(select foo from table t where t.id in (1,2)) as res1,
(select foo from table t where t.id in (3,4)) as res2

But all I get is error:

Subquery returned more than 1 value

Result that I need:

res1 res2
foo1 foo3
foo2 foo4

How can I get such result using only one query?

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
Calliso
  • 31
  • 6
  • What you are trying to get does not make sense. Please EDIT your post. Please provide the actual table structure and sample data that gives the "foo" result content. Also add in your edit what is the context that "foo1" and "foo3" are on one row, vs "foo2" and "foo4" on the second. Many times, generic 'help' options with crud sample data dont give a better picture of what you are trying to really accomplish. Might be best to put better context to why you are querying the way you are. Dont provide any private/confidential, but do provide accurate context. – DRapp Dec 11 '21 at 04:09

1 Answers1

0

There is no need for subqueries, you can use UNION ALL. Sample:

select foo as res1 from table t where t.id in (1,2)
union all 
select foo as res2 from table t where t.id in (3,4)
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • Unfortunetely 'UNION' takes the column names only from the first query so all data will be returned as res1 – Calliso Dec 11 '21 at 15:51
  • Sorry, I misunderstood the question. I don't know what DataBase you are using, so on PostgreSQL, you can write this in one query that: `select unnest(array_agg(distinct t1.foo)), unnest(array_agg(distinct t2.foo)) from t as t1 inner join t as t2 on t2.id in (3,4) where t1.id in (1,2)` – Ramin Faracov Dec 15 '21 at 21:25