Return multiple rows from multi SELECT subquery

Question

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?

Do these table have auto increment id fields? If yes, you can use those to join them. — Shadow, Nov 12 '15 at 13:22
@GarethD the tuples do not matter. All I am attempting to do is display all data form those 3 tables. Data can be unordered — wmash, Nov 12 '15 at 13:28

score 4 · Accepted Answer · answered Nov 12 '15 at 13:20

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;

Brilliant answer!! Thank you. Never thought of the variables — wmash, Nov 12 '15 at 13:34

Return multiple rows from multi SELECT subquery

1 Answers1