Make multiple queries that can't be subqueries in same simple SELECT lead into single multirow result using MySQL

Question

I have a specific thing in mind that can't be solved by a very simple multi-subquery SELECT, shown on the bottom: take the query 1

SELECT playername AS Rookie FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 129600 AND onlinetime < 648000 ORDER BY playername;

which returns result like

+------------------+
| Rookie           |
+------------------+
| _Golden_Apple    |
| _MineTrick_      |
+------------------+

(just a portion of the whole result) and query 2

SELECT playername AS Eager FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 648000 AND onlinetime < 1296000 ORDER BY playername;

that returns like (just a portion of the whole result)

+-----------------+
| Eager           |
+-----------------+
| 1Herofox        |
| 1lyndon         |
+-----------------+

The idea is to combine those columns to get something like this:

+------------------+-----------------+
| Rookie           | Eager           |
+------------------+-----------------+
| _Golden_Apple    | 1Herofox        |
| _MineTrick_      | 1lyndon         |
+------------------+-----------------+

A query like

SELECT
    (SELECT playername FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 129600 AND onlinetime < 648000) AS Rookie,
    (SELECT playername FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 648000 AND onlinetime < 1296000) AS Eager
;

leads into 1242 (21000): Subquery returns more than 1 row. Googling is a problem because I have no idea what words to use; used

mysql merge subqueries that return multiple rows
mysql multiple columns in result from subqueries
mysql select multiple queries as columns

just to get something totally different.

The result is mostly for human viewing only and the rows have nothing to do with other columns', so what means are used to merge those columns like that doesn't matter as long as performance isn't dropped too much; given they are not related at all, makes JOINs look like wrong tool.

That's where my understanding stops.

score 0 · Answer 1 · answered Jun 07 '15 at 20:13

0

You're looking for UNION:

SELECT playername, 'Rookie' as Rank
  FROM MinecraftMySQL.`lb-players`
  WHERE onlinetime >= 129600 AND onlinetime < 648000
UNION
SELECT playername, 'Eager' as Rank
  FROM MinecraftMySQL.`lb-players`
  WHERE onlinetime >= 648000 AND onlinetime < 1296000
ORDER BY playername;

answered Jun 07 '15 at 20:13

Martin

6,632
4
25
28

It doesn't produce the result I'm looking for cuz the one I'm looking for is much simpler to read. – rautamiekka Jun 07 '15 at 20:16
1

Unfortunately simpler to read doesn't mean that it's the correct solution. And the subselect solutions are sub-obtimal and harder to read. What happens if there's a different number of Rookies vs Eager's? You should format it for the users to read in your application :) – Martin Jun 07 '15 at 20:37
There is a big difference in the row count between the multiple subqueries (these 2 aren't the only ones), but how simple it's for a computer to read doesn't matter as long as performance to fetch the result isn't killed. – rautamiekka Jun 07 '15 at 20:39

rcheuk · Answer 2 · 2015-06-07T20:24:12.530

0

Try:

SELECT Rookie.playername as Rookie, Eager.playername as Eager from 
    (SELECT distinct playername FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 129600 AND onlinetime < 648000) Rookie 
JOIN 
    (SELECT distinct playername FROM MinecraftMySQL.`lb-players` WHERE onlinetime >= 648000 AND onlinetime < 1296000) Eager
;

edited Jun 07 '15 at 20:24

answered Jun 07 '15 at 20:21

rcheuk

1,140
1
12
32

That's much closer, but oddly enough the second column repeats a single name for so many rows before showing another name :O – rautamiekka Jun 07 '15 at 20:22
add distinct in front of playername for the second query; updated above – rcheuk Jun 07 '15 at 20:23
Tested by adding `DISTINCT` to both sub-`SELECT`s, it seems to do something but doesn't affect the primary issue mentioned above. – rautamiekka Jun 07 '15 at 20:25
if you run the sub selects separately, do they return single results? – rcheuk Jun 07 '15 at 20:28
i see what's happening. will try to figure it out and get back to you – rcheuk Jun 07 '15 at 20:34
For the record: changed the `JOIN` keyword to `STRAIGHT_JOIN` with no difference in result. – rautamiekka Jun 07 '15 at 20:37
what you're looking for is a full outer join. https://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – rcheuk Jun 07 '15 at 20:42
Is it really possible since the rows from the queries have nothing to do with each other, apart from being part of the result dictated by the time ? – rautamiekka Jun 07 '15 at 21:25
I would think you can do it, if you join it against itself to get the left and right joins. But by that point, you're creating an overly complex query for something that would work better as two separate queries. perhaps not the answer you seek, but in the end, if might be better for you to retrieve the data as two separate queries - rookie and eager and use your front end to display how you want it to be displayed – rcheuk Jun 08 '15 at 10:55

Make multiple queries that can't be subqueries in same simple SELECT lead into single multirow result using MySQL

2 Answers2