WHERE IN with REPLACE-Statement

Question

I have 2 Tables ...provision and tariff. Table provision stored related Tariff-IDs as pipe-separated Value. This Query shows me only the first tariff name sqlfiddle

SELECT
    GROUP_CONCAT(tariff_name)
FROM
    tariff
WHERE
    tariff_id IN(REPLACE('1272|1312|1314', '|', ','))

I want this query use as a Subquery where '1272|1312|1314'is the joined field from provision. Just like...

SELECT
    provision.id,
    provision.tariff_id,
    tariff.id,
    ( SELECT GROUP_CONCAT( tariff_name ) FROM tariff WHERE tariff_id IN(REPLACE (provision.tariff_id, '|', ',' ))) AS tn
FROM
    provision
    LEFT JOIN tariff ON tariff.id = provision.tariff_id

This is a very old project and refactoring is not an option!

Post sample data for both tables and expected results to clarify what you want. — forpas, Aug 25 '21 at 15:11
Generally when we have to interact with a character delimited list in a database, the best course of action is to split the delimited list into individual rows (one for each token in the list). Mysql doesn't have built in functionality to do that directly, but [there are some tricks to get you there](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows). The result will be a derived table that you can join just like any other table to get to your results. Unfortunately with your ugly data, the performance is going to be ugly as well. — JNevill, Aug 25 '21 at 15:14
@forpas [sqlfiddle](http://sqlfiddle.com/#!9/0c3777e/1) ... the expected Result for Column `tn` is a comma separated String with related tariffnames. But i get only the first tariffname — Sirko.uhl, Aug 26 '21 at 10:56

score 0 · Accepted Answer · answered Aug 26 '21 at 11:10

0

Something like this,but the real answer is to normalize your design

SELECT
    provision.id,
    provision.tariff_ids,
    tariff.id,
    GROUP_CONCAT( name ) AS tn
FROM
    provision
    LEFT JOIN tariff ON FIND_IN_SET(tariff.id,REPLACE(provision.tariff_ids,"|",","))
    GROUP BY provision.id

http://sqlfiddle.com/#!9/0c3777e/20

answered Aug 26 '21 at 11:10

Mihai

26,325
7
66
81

...thx a lot @Mihai . Works like a charm. To clarify ...i know, normalize the design is necessary, but to complex for me alone :-( – Sirko.uhl Aug 26 '21 at 13:49

WHERE IN with REPLACE-Statement

1 Answers1