CODEWITHSUNDEEP
sqlpostgresql
आनंद
आनंद

Reputation: 2582

Retrieving multiple sub queries output with different rows into a single response in postgresql

I'd like to receive the output of a few sub queries(each of them return a single column with different number of rows) into a single response.

I'm trying the following but it doesn't work with error as [21000] ERROR: more than one row returned by a subquery used as an expression.

SELECT
  (SELECT DISTINCT org_name
   FROM table_1
   WHERE column1= 1)                        AS terminal_1,
  (SELECT org_name
   FROM table_5
   WHERE column5= 1)                          AS cfs_1,
  (SELECT DISTINCT org_name
   FROM table_4 where column7= 136 )     AS terminals_2,
  (SELECT org_name
   FROM table_6
   WHERE column1=7)                           AS cfs_2;

I'd like to do in the way such that in future I can add a few more sub-queries, each of which will also return a single column with a different number of rows. Is there a good practice to achieve the same? Thank you.

EDIT 1: The output response could be in any logical form. i.e. tabular or JSON.

Upvotes: 3

Views: 2601

Answers (2)

Hambone
Hambone

Reputation: 16397

What about making us of PostgreSQL's excellent arrays?

with terminal_1 as (
  SELECT array_agg (DISTINCT org_name) as term1_name
  FROM table_1
  WHERE column1= 1
),
cfs_1 as (
  SELECT array_agg (org_name) as cfs1_name
  FROM table_5
  WHERE column5= 1
),
terminals_2 as (
  SELECT array_agg (DISTINCT org_name) as term2_name
  FROM table_4 where column7= 136
),
cfs_2 as (
  SELECT array_agg (org_name) as cfs2_name
  FROM table_6
  WHERE column1=7
)
select
  term1_name, cfs1_name, term2_name, cfs2_name
from
  terminal_1
  cross join cfs_1
  cross join terminals_2
  cross join cfs_2

While this doesn't perform the cosmetics of what you want, I believe it does deliver the content the way you intended.

Upvotes: 4

Radim Bača
Radim Bača

Reputation: 10711

I believe you simply need UNION ALL

SELECT DISTINCT  't1' tableNum, org_name
FROM table_1
WHERE column1= 1
UNION ALL
SELECT 't5' tableNum, org_name
FROM table_5
WHERE column5= 1
UNION ALL
SELECT DISTINCT 't4' tableNum, org_name
FROM table_4 
where column7= 136
UNION ALL
SELECT 't6' tableNum, org_name
FROM table_6
WHERE column1=7

Upvotes: 2

Related Questions