CODEWITHSUNDEEP
mysqlpresto
Nipun
Nipun

Reputation: 4319

sql presto query to join 2 tables interatably

I need to do this in the sql query. LEt me know if this possible

I have a table which has a mapping like (table1)

num,value
2,'h'
3,'b'
5,'c'

Now I have another table which have these values (table2)

name, config
"test1",45
"test2",20

Now what I want is the sql query which will add another column to my table2 by checking if config column values are divisible by table1.num and if yes concat the table1.values to it

so now after the sql query it should become

name, config, final
"test1",45, bc
"test2",20, hc

Please let me know if I can form a query for this

Upvotes: 0

Views: 3187

Answers (2)

Ike Walker
Ike Walker

Reputation: 65567

The answer from P.Salmon should work for MySQL. If you are using Presto, then this would work:

SELECT t2.name,
       t2.config,
       array_join(array_agg(t1.value),'','') AS final
FROM table1 t1
CROSS JOIN table2 t2
WHERE t2.config % t1.num = 0
GROUP BY t2.name,
         t2.config

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17655

You can by using a cross join,the mod function https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_mod and group_concat https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

select t2.name,t2.config,group_concat(t1.value separator '') final
from table1 t1
cross join table2 t2
where t2.config % t1.num = 0
group by t2.name,t2.config

+-------+--------+-------+
| name  | config | final |
+-------+--------+-------+
| test1 |     45 | bc    |
| test2 |     20 | hc    |
+-------+--------+-------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions