MySQL - link tables issue (select unique rows)

Question

I have been trying to sort it out but failed. I have below snapshot of my problem and it has been cut. What I want is the following:

action_id   event_id    link    text    duration
339054      10221877    317547  text1   30
339057      10221877    317548  text2   45
339061      10221877    317549  text3   30
339065      10221877    317551  text4   40
339074      10221877    317550  text5   60
339075      10221877    317552  text6   80

Is that even possible with just two tables? if not, what might be helpful?

CREATE TABLE time1
(

prim int (5),
booking int (5) PRIMARY KEY,
duration int (5));

insert into time1 values 
( 10221877, 296480,30),
( 10221877, 296484,45),
( 10221877, 296487,30),
( 10221877, 296492,40),
( 10221877, 296494,60),
( 10221877, 296510,80),
( 10221877, 296511,11);


CREATE TABLE action1
(

action_id int (5) PRIMARY KEY,
event_id int (5),
link int (5),
text_text VARCHAR(255)

);

insert into action1 values 
(339054,10221877,317547,"text1"),
(339057,10221877,317548,"text2"),
(339061,10221877,317549,"text3"),
(339065,10221877,317551,"text4"),
(339074,10221877,317550,"text5"),
(339075,10221877,317552,"text6");

"if not, what might be helpful" - Primary keys and a clear relation definition. — Paul Spiegel, Apr 23 '19 at 14:13
Do any of the columns besides time1.prim and action1.event_id relate to each other? Since we are only seeing a portion of the data and don't know what the foreign keys are, it is entirely possible that either action1.actionid or action1.link could correspond to time1.booking. If so, you could also join on `link = booking` or `action_id = booking` and that give you the data that you need. — derek.wolfe, Apr 23 '19 at 16:43

Maksym Fedorov · Answer 1 · 2019-04-24T06:16:19.933

As I can see you want to join the first row of the action1 table to the first row of the time1 table, second row of the action1 table to the second row of time1 and so on. So you can use variables to calculate an ordinal number of rows and join tables by it. For example:

SELECT a.action_id, a.event_id, a.link, a.text_text, t.duration
FROM (SELECT action_id,
             event_id,
             link,
             text_text,
             IF(@event_id = event_id, @rownumber := @rownumber + 1, @rownumber := 0) as rownumber,
             @event_id := event_id
      FROM action1,
           (SELECT @event_id := 0, @rownumber := 0) AS rn) as a
         LEFT JOIN
     (SELECT duration,
             prim,
             IF(@prim = prim, @rownumber := @rownumber + 1, @rownumber := 0) as rownumber,
             @prim := prim
      FROM time1,
           (SELECT @prim := 0, @rownumber := 0) AS rn) as t ON a.event_id = t.prim AND a.rownumber = t.rownumber

The result is

--------------------------------------------------------
| action_id | event_id | link   | text_text | duration |
--------------------------------------------------------
|    339054 | 10221877 | 317547 | text1     |       30 |
|    339057 | 10221877 | 317548 | text2     |       45 |
|    339061 | 10221877 | 317549 | text3     |       30 |
|    339065 | 10221877 | 317551 | text4     |       40 |
|    339074 | 10221877 | 317550 | text5     |       60 |
|    339075 | 10221877 | 317552 | text6     |       80 |
--------------------------------------------------------

DEMO in DBFiddle

This return `duration` value as null from the second row to the last, in my testing and in your fiddle as well. — FanoFN, Apr 24 '19 at 02:42
@tcadidot0 oh sorry. I made a mistake. I fixed it. Check my answer, please — Maksym Fedorov, Apr 24 '19 at 06:17
Yes, this works now. I've tested on MariaDB 10.1, 10.3 and MySQL 4.1. And your fiddle 5.7 ;) — FanoFN, Apr 24 '19 at 06:28

score 1 · Answer 2 · answered Apr 23 '19 at 11:07

1

What you want looks kinda like a JOIN, take a look at this answer right here as it has a lot of info about joins, and I would try to do the following query

SELECT * FROM `time1` INNER JOIN `action1` ON `time1`.`prim` = `action1`.`event_id`

if I understood the data you entered correctly.

answered Apr 23 '19 at 11:07

AwesomeGuy

1,059
9
28

1

that is the issue here. it duplicates results and I have done that. the issue here is to have results as per my question above. – Kalenji Apr 23 '19 at 11:10
the above query looks correct and would generate your desired result - if you can kindly update your question – nightfury Apr 23 '19 at 11:13
Well, as I can see, the problem is your data, the column prim and event_id have all the same values, so when you're trying to join those two tables, it shows all rows from one table for every row in the other, you need one `UNIQUE ID` and in the other table another column that references that unique column – AwesomeGuy Apr 23 '19 at 11:16
1

@Kalenji, OfCourse it will give duplicate records. Because there is no `unique` column between two tables – Arun Palanisamy Apr 23 '19 at 11:18
and thats the challange. if there was a unique column i would not ask this question. Anyone else has got some ideas? – Kalenji Apr 23 '19 at 11:21
Would some kind of union work (union with condition)? – Kalenji Apr 23 '19 at 11:34

Arun Palanisamy · Answer 3 · 2019-04-23T12:33:21.917

You can try something like below with the windows functions to get the result. Here i'm joining 2 tables and assigning row_number() for each rows and then assigning group number by using DENSE_RANK() then getting the results by matching rownumber and groupnumber. Hope this helps.

select action_id,event_id,link,text_text,duration from (
    select action1.*,duration,(row_number() over (partition by action1.action_id)) as rowno,
           (dense_rank() over (order by action1.action_id)) as groupno  from action1
    join time1 
    on action1.event_id = time1.prim
) as mytable
where mytable.rowno = mytable.groupno

You can check the results here : DEMO HERE

score 0 · Answer 4 · answered Apr 23 '19 at 12:54

I got this to work using window functions on MySQL 8.0:

SELECT a.action_id, a.event_id, a.link, a.text_text, t.duration
FROM (SELECT *, ROW_NUMBER() OVER () AS rownum FROM time1 ORDER BY booking) AS t
JOIN (SELECT *, ROW_NUMBER() OVER () AS rownum FROM action1 ORDER BY action_id) AS a
  USING (rownum)

Output:

+-----------+----------+--------+-----------+----------+
| action_id | event_id | link   | text_text | duration |
+-----------+----------+--------+-----------+----------+
|    339054 | 10221877 | 317547 | text1     |       30 |
|    339057 | 10221877 | 317548 | text2     |       45 |
|    339061 | 10221877 | 317549 | text3     |       30 |
|    339065 | 10221877 | 317551 | text4     |       40 |
|    339074 | 10221877 | 317550 | text5     |       60 |
|    339075 | 10221877 | 317552 | text6     |       80 |
+-----------+----------+--------+-----------+----------+

I have to comment it's unconventional to join rows based on their row number. There isn't supposed to be any implicit order to the rows, and you should join them only by value, not by row position.

In your example the booking number seems to be increasing, but I don't know if that's a reliable assumption in your data, or a coincidence in your example. And I don't know if I'm making the right assumption that you want the rows joined by position. That matches your example, but again it could just be coincidental.

I also can't tell from your example how you want to do the join when there are more than one event_id values. Do you want the row numbering to start over, or just match row by row, by row_number()? What if there are a different number of rows per event_id in the two tables? Do you need to use LEFT OUTER JOIN? Or FULL OUTER JOIN?

MySQL - link tables issue (select unique rows)

4 Answers4