Mysql - dynamic UNION with multiple recieve ids

Question

how to convert this multiple UNION to simple query?

number of artist_id is dynamic.

45,122,95

or

100,20

or

89,9449

or

22,495,700,98

....

(SELECT b.`id`, b.`id`, b.`date`, b.`artist`, b.`title`, b.`photo`
FROM `tags_mp3s` a
INNER JOIN `mp3s` b ON b.`id` = a.`mp3_id` AND a.`artist_id` = 45
ORDER BY b.`date` DESC
LIMIT 5)

UNION ALL

(SELECT b.`id`, b.`id`, b.`date`, b.`artist`, b.`title`, b.`photo`
FROM `tags_mp3s` a
INNER JOIN `mp3s` b ON b.`id` = a.`mp3_id` AND a.`artist_id` = 122
ORDER BY b.`date` DESC
LIMIT 5)

UNION ALL

(SELECT b.`id`, b.`id`, b.`date`, b.`artist`, b.`title`, b.`photo`
FROM `tags_mp3s` a
INNER JOIN `mp3s` b ON b.`id` = a.`mp3_id` AND a.`artist_id` = 95
ORDER BY b.`date` DESC
LIMIT 5)

.....

thanks

@farhadamjady because i want `ORDER BY b.`date` DESC` for each `artist_id`. in `where in()` select all according `id` then i not want. — Farzad, Jun 17 '17 at 08:23
ok, you dont want to use server side language? just native sql? — farhadamjady, Jun 17 '17 at 08:31
@farhadamjady i want use server side. in function. PHP mysql: `function getrelated() { ..query and get related rows... }` — Farzad, Jun 17 '17 at 08:34
Maybe use combination of IN (as per farhadamjady) and "fake row-over" - see: https://stackoverflow.com/q/1895110/1662973 — Anthony Horne, Jun 17 '17 at 08:34
no i mean something like loop for generating the main query then use it, if not you can use for loop in sql — farhadamjady, Jun 17 '17 at 08:39
@farhadamjady how to loop in sql? i can generate this select union. but i find best simple solution — Farzad, Jun 17 '17 at 08:43
@farhadamjady you cannot loop in SQL without stored procedure which in some cases didn't work when sending queries just to fetch some data in dynamic condition — greendino, Feb 18 '21 at 05:35

Gordon Linoff · Accepted Answer · 2017-06-17T20:40:37.780

1

In MySQL, you can use variables:

SELECT ta.*
FROM (SELECT m.*,
             (@rn := if(@a = t.artist_id, @rn + 1,
                        if(@a := t.artist_id, 1, 1)
                       )
             ) as rn
      FROM tags_mp3s t INNER JOIN
           mp3s m
           ON m.id = t.mp3_id CROSS JOIN
           (SELECT @rn := 0, @a := -1) params
      WHERE t.artist_id IN (. . . )
      ORDER BY t.artist_id, m.date DESC
     ) ta
WHERE rn <= 5;

edited Jun 17 '17 at 20:40

answered Jun 17 '17 at 12:08

Gordon Linoff

1,242,037
58
646
786

thanks, tested but, `#1051 - Unknown table 't'` and `Unrecognized keyword. (near "CROSS" at position 278)` – Farzad Jun 17 '17 at 12:22
Gordon Linoff: `ORDER BY m.date DESC` doesnt work. sorted by `t.artist_id`. why? – Farzad Jun 18 '17 at 07:54
@grizzly . . . I don't understand the comment. There is no `ORDER BY m.date DESC` in the query. – Gordon Linoff Jun 18 '17 at 12:56
`m.date DESC` work for after selected rows, and this no need. i want newest rows from `mp3s` table according `date` column. 2017,2016,2015,...... but this query sort rows according first ids. so first ids have oldest date – Farzad Jun 18 '17 at 14:49
`m.date DESC` work for after selected rows, and this no need. i want newest rows from `mp3s` table according `date` column. 2017,2016,2015,....... but this query sort rows according first ids. so first ids have oldest date: 2008,2007,2004,2003,......while 2017,2016,...... are newest tracks – Farzad Jun 18 '17 at 14:56
@grizzly . . . Are you sure that `ORDER BY` is `ORDER BY t.artist_id, m.date DESC`? The first key has to be the artist. – Gordon Linoff Jun 18 '17 at 15:00
yes, `t.artist_id` not important for me. i need select 5 latest new track for each artist according song date. song dates in all of the time. – Farzad Jun 18 '17 at 15:32

Mysql - dynamic UNION with multiple recieve ids

1 Answers1