wmash wmash - 4 months ago 18
SQL Question

Return multiple rows from multi SELECT subquery

query

SELECT
(SELECT NAME FROM product_component) AS pcNAME,
(SELECT PROJECT_NAME FROM jira_project) AS jpNAME,
(SELECT FILTER_NAME FROM jira_filter) AS jfNAME


Each SELECT will return an indeterminate number of rows. I get the error
Subquery returns more than 1 row
. My desired output will be something like this (quick sketch):

=======================================
| pcNAME | jpNAME | jfNAME |
=======================================
| data | data | data |
+------------+------------+-----------+
| data | data | data |
+------------+------------+-----------+
| data | data | data |
+------------+------------+-----------+
| | data | data |
+------------+------------+-----------+
| | data | data |
+------------+------------+-----------+
| | data | |
+------------+------------+-----------+


Each column may produce a different number of rows than the others. So I will want to produce the amount of rows from the max and then blank out the others that don't fill the max number of rows.

NOTE: None of these tables have a shared column so cannot achieve as
INNER JOIN


Any ideas on how this can be achieved?

Answer

One way to handle this in MySQL to use to variables, union all and aggregation:

SELECT MAX(NAME) as NAME, MAX(PROJECT_NAME) as PROJECT_NAME,
       MAX(FILTER_NAME) as FILTER_NAME
FROM ((SELECT (@rnpc := @rnpc + 1) as rn, NAME, NULL as PROJECT_NAME, NULL as FILTER_NAME
      FROM product_component CROSS JOIN
           (SELECT @rnpc := 0) params
      ) UNION ALL
      (SELECT (@rnpn := @rnpn + 1) as rn, NULL, PROJECT_NAME, NULL as FILTER_NAME
      FROM jira_project CROSS JOIN
           (SELECT @rnpn := 0) params
      ) UNION ALL
      (SELECT (@rnf := @rnf + 1) as rn, NAME, NULL as PROJECT_NAME, NULL as FILTER_NAME
      FROM jira_filter CROSS JOIN
           (SELECT @rnf := 0) params
      )
     ) t
GROUP BY rn
ORDER BY rn;