How to join two selects from the same table in mysql

Question

Ï need to do a join of two selects of the same table (top and bottom rows), but the inner join returns an empty set. I do not understand how this is possible since I am doing the join on a new column which is the same for both tables.

Here are the select sub-queries for the bottom-row and top-row tables:

bottom rows:

select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10;

which gives the result:

+------------+----------+------------+-------+---------+-----------+
| row_number | username | timestamp  | game  | viewers | followers |
+------------+----------+------------+-------+---------+-----------+
|          1 | user1    | 1517443140 | game1 |       3 |     44669 |
|          2 | user2    | 1517443081 | game2 |       5 |     44668 |
|          3 | user1    | 1517443080 | game1 |       3 |     44668 |
|          4 | user2    | 1517443021 | game2 |       5 |     44667 |
|          5 | user1    | 1517443020 | game1 |       3 |     44667 |
|          6 | user2    | 1517442961 | game2 |       5 |     44666 |
|          7 | user1    | 1517442960 | game1 |       3 |     44666 |
|          8 | user2    | 1517442901 | game2 |       5 |     44665 |
|          9 | user1    | 1517442900 | game1 |       3 |     44665 |
|         10 | user2    | 1517442841 | game2 |       5 |     44664 |
+------------+----------+------------+-------+---------+-----------+

and

top rows:

select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10;

which gives the result:

+------------+----------+------------+-------+---------+-----------+
| row_number | username | timestamp  | game  | viewers | followers |
+------------+----------+------------+-------+---------+-----------+
|          1 | user1    | 1514764860 | game1 |       3 |        31 |
|          2 | user2    | 1514764861 | game2 |       5 |        31 |
|          3 | user1    | 1514764920 | game1 |       3 |        32 |
|          4 | user2    | 1514764921 | game2 |       5 |        32 |
|          5 | user1    | 1514764980 | game1 |       3 |        33 |
|          6 | user2    | 1514764981 | game2 |       5 |        33 |
|          7 | user1    | 1514765040 | game1 |       3 |        34 |
|          8 | user2    | 1514765041 | game2 |       5 |        34 |
|          9 | user1    | 1514765100 | game1 |       3 |        35 |
|         10 | user2    | 1514765101 | game2 |       5 |        35 |
+------------+----------+------------+-------+---------+-----------+

My problem is that the inner join of both tables gives an empty set as a result, and that either the left or the right join give null columns on the opposite side. For example:

select late.row_number, early.row_number, early.username as early_users, late.username as late_users, early.followers as early_followers, late.followers as late_followers from 
(select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10) late 
left join 
(select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10) early 
on late.row_number = early.row_number;

returns:

+------------+------------+-------------+------------+-----------------+----------------+
| row_number | row_number | early_users | late_users | early_followers | late_followers |
+------------+------------+-------------+------------+-----------------+----------------+
|          1 |       NULL | NULL        | user1      |            NULL |          44669 |
|          2 |       NULL | NULL        | user2      |            NULL |          44668 |
|          3 |       NULL | NULL        | user1      |            NULL |          44668 |
|          4 |       NULL | NULL        | user2      |            NULL |          44667 |
|          5 |       NULL | NULL        | user1      |            NULL |          44667 |
|          6 |       NULL | NULL        | user2      |            NULL |          44666 |
|          7 |       NULL | NULL        | user1      |            NULL |          44666 |
|          8 |       NULL | NULL        | user2      |            NULL |          44665 |
|          9 |       NULL | NULL        | user1      |            NULL |          44665 |
|         10 |       NULL | NULL        | user2      |            NULL |          44664 |
+------------+------------+-------------+------------+-----------------+----------------+

When, what I would like to get is:

+------------+------------+-------------+------------+-----------------+----------------+
| row_number | row_number | early_users | late_users | early_followers | late_followers |
+------------+------------+-------------+------------+-----------------+----------------+
|          1 |          1 | user1       | user1      |              31 |          44669 |
|          2 |          2 | user2       | user2      |              31 |          44668 |
|          3 |          3 | user1       | user1      |              32 |          44668 |
|          4 |          4 | user2       | user2      |              32 |          44667 |
|          5 |          5 | user1       | user1      |              33 |          44667 |
|          6 |          6 | user2       | user2      |              33 |          44666 |
|          7 |          7 | user1       | user1      |              34 |          44666 |
|          8 |          8 | user2       | user2      |              34 |          44665 |
|          9 |          9 | user1       | user1      |              35 |          44665 |
|         10 |         10 | user2       | user2      |              35 |          44664 |
+------------+------------+-------------+------------+-----------------+----------------+

Can anyone please tell me what I am doing wrong/how to achieve what I want?

Thank you very much in advance :)

Don't use the same named user variable twice in the same query. — P.Salmon, Jun 16 '19 at 12:13

score 0 · Accepted Answer · answered Jun 16 '19 at 21:10

First of all, and this has nothing to with anything, you are not doing an INNER JOIN but rather an OUTER JOIN.

I wish I had the definitive explanation as to why the computed row numbers do not behave the same way as actual database columns in performing outer joins, but that seems to be the case. You should therefore be creating temporary tables where these computed row numbers will become actual columns:

CREATE TEMPORARY TABLE late as
    select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10;

CREATE TEMPORARY TABLE early as
    select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp, username limit 10;

select late.row_number, early.row_number, early.username as early_users, late.username as late_users, early.followers as early_followers, late.followers as late_followers from
late l JOIN early on late.row_number = early.row_number;

Now that the row numbers are in actual columns, there is no need to do an outer join; an inner join will suffice. Had you used an inner join without the temporary tables, you would have returned no rows and that's probably why you resorted to the outer join.

By the way, the advice to use unique column names becomes crucial if you need to process these values in a program.

If you would be joining many rows (here you only have 10 in each table), you would then consider creating an index on the row_number columns of the temporary tables, probably by just defining them as primary keys.

How to join two selects from the same table in mysql

1 Answers1