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
– Shadow, Commented Nov 12, 2015 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
– wmash, Commented Nov 12, 2015 at 13:28

Gordon Linoff · Accepted Answer · 2015-11-12 13:20:36Z

4

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;

answered Nov 12, 2015 at 13:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wmash Over a year ago

Brilliant answer!! Thank you. Never thought of the variables

Collectives™ on Stack Overflow

Return multiple rows from multi SELECT subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related