Right join / inner join / multiselect [MYSQL] TABLE RESULTS

Question

I have a big trouble to find a correct way to select a column from another table, and show one results that would contain two tables in the same time.

First table:

id | times     | project_id |
12 | 12.24     | 40         |
13 | 13.22     | 40         |
14 | 13.22     | 20         |
15 | 12.22     | 20         |
16 | 13.30     | 40         |

Second table:

id | times     | project_id |
32 | 22.24     | 40         |
33 | 23.22     | 40         |
34 | 23.22     | 70         |
35 | 22.22     | 70         |
36 | 23.30     | 40         |

I expect to select all the times from the first table for project_id =40, and join to this times from the second table for the same project_id =40.

The results should be like this below:

id | time      | time       | project_id |
12 | 12.24     | 22.24      | 40         |
13 | 13.22     | 23.22      | 40         |
16 | 13.30     | 23.30      | 40         |

Why would the time `12.24` in the first table join to the time `22.24` in the second table? Just by the order of the rows? The reason this is difficult is that rows in a table have no implicit order or ranking. Joins work by matching values, not by row positions. — Bill Karwin, Sep 30 '17 at 19:47
Yes times are different. I want to select just by the order of the rows, that's all. — Valerius Romulus, Sep 30 '17 at 20:00

Paul Maxwell · Answer 1 · 2017-10-01T08:43:14.567

You need to use UNION ALL between those 2 tables otherwise you will get incorrect results. Once you have all the rows together then you can use variables to carry over "previous values" such as shown below and demonstrated at this SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int, `times` decimal(6,2), `project_id` int)
;

INSERT INTO Table1
    (`id`, `times`, `project_id`)
VALUES
    (12, 12.24, 40),
    (13, 13.22, 40),
    (14, 13.22, 20),
    (15, 12.22, 20),
    (16, 13.30, 40)
;


CREATE TABLE Table2
    (`id` int, `times` decimal(6,2), `project_id` int)
;

INSERT INTO Table2
    (`id`, `times`, `project_id`)
VALUES
    (32, 22.24, 40),
    (33, 23.22, 40),
    (34, 23.22, 70),
    (35, 22.22, 70),
    (36, 23.30, 40)
;

Query 1:

select
      project_id, id, prev_time, times
from (
    select
           @row_num :=IF(@prev_value=d.project_id,@row_num+1,1) AS RowNumber
         , d.*
         , IF(@row_num %2 = 0, @prev_time, '') prev_time
         , @prev_value := d.project_id
         , @prev_time := times
     from (
          select `id`, `times`, `project_id` from Table1
          union all
          select `id`, `times`, `project_id` from Table2
          ) d
    cross join (select @prev_value := 0, @row_num := 0) vars
    order by d.project_id, d.times
    ) d2
where prev_time <> ''

Results:

| project_id | id | prev_time | times |
|------------|----|-----------|-------|
|         20 | 14 |     12.22 | 13.22 |
|         40 | 13 |     12.24 | 13.22 |
|         40 | 32 |     13.30 | 22.24 |
|         40 | 36 |     23.22 |  23.3 |
|         70 | 34 |     22.22 | 23.22 |

Note: MySQL doe snot currently support LEAD() and LAG() functions when this answer was prepared. When MySQL does support these that approach would be simpler and probably more efficient.

select
       d.*
from (
     select
            d1.*
          , LEAD(times,1) OVER(partition by project_id order by times ASC) next_time
     from (
          select id, times, project_id from Table1
          union all
          select id, times, project_id from Table2
          ) d1
      ) d
where next_time is not null

Right join / inner join / multiselect [MYSQL] TABLE RESULTS

1 Answers1