Mysql Join Two Tables With Recycling

join, mysql

Solution

Assuming you have `PRIMARY KEY` on column `id` of table `palette_tbl`, this would do the trick for you:

SELECT name,
       (SELECT rgb FROM palette_tbl WHERE id = @row_id) AS rgb,
       (@row_id:= IF(@row_id = @cnt, 1, @row_id + 1)) AS dummy_id
FROM zoo_tbl a, (SELECT @row_id:= 1, @cnt := (SELECT COUNT(1) FROM palette_tbl)) dummy;

SQLFIDDLE DEMO HERE

Problem

Suppose I have two tables: ``` zoo_tbl name ------ dog cat monkey lion tiger elephant fish palette_tbl rgb ------ pink yellow green ``` I want to do a join on the two tables such that the rgb rows repeat in a cycle. ``` name rgb --------------------- dog pink cat yellow monkey green lion pink tiger yellow elephant green fish pink ``` How should I build this query? I have an idea to create a large temporary table with recurring rgb values before joining, but even if I do that, I would have to enumerate the zoo_tbl and the temporary table before doing a join. There has to be a simpler/more direct way to do this...

Original source