Split and Join in Where clause MySql

Question

I have two tables.

Table First

+----+---------+-------+
| ID | Name    | URl   |
+----+---------+-------+
| 1  | Product | http1 |
+----+---------+-------+
| 2  | Cheif   | http2 |
+----+---------+-------+
| 3  | Client  | http3 |
+----+---------+-------+

Table Second
+-----+----------------+-------------+
| ID  | Dashboard      | Definition  |
+-----+----------------+-------------+
| 1.1 | Product,Cheif  | Overview    |
+-----+----------------+-------------+
| 2.1 | Cheif          | malpractice |
+-----+----------------+-------------+
| 3.1 | Client,Product | Vanity      |
+-----+----------------+-------------+

I am expecting output

+----+----------------+-------------+-------------+
| ID | Dashboard      | Definition  | Url         |
+----+----------------+-------------+-------------+
| 1  | Product,Cheif  | Overview    | http1,http2 |
+----+----------------+-------------+-------------+
| 2  | Cheif          | malpractice | http2       |
+----+----------------+-------------+-------------+
| 3  | Client,Product | Vanity      | http3,http1 |
+----+----------------+-------------+-------------+

I tried with split using if, locate, substring then creating view, only to find alias cannot be used on where clause.

split logic is as below.

SELECT IF(
        LOCATE(‘,’, `dashboard`) > 0,
        SUBSTRING(`dashboard`, 1, LOCATE(‘,’, `dashboard`) - 1),
        `u_dashboard`
    ) AS memberfirst,
    IF(
        LOCATE(‘,’, `dashboard`) > 0,
        SUBSTRING(`dashboard`, LOCATE(‘,’, `dashboard`) + 1),
        NULL
    ) AS memberlast
FROM `table`;

I would recommend you bring your database desing to 3NF at least. This is not even 1NF. — The Impaler, Oct 02 '20 at 11:38
https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — sticky bit, Oct 02 '20 at 11:49

forpas · Accepted Answer · 2020-10-02T12:30:57.880

You can use FIND_IN_SET() to join the tables and aggregate with GROUP_CONCAT():

select t2.id, t2.dashboard, t2.definition,
       group_concat(t1.url order by find_in_set(t1.name, t2.dashboard)) url
from TableSecond t2 left join TableFirst t1
on find_in_set(t1.name, t2.dashboard)
group by t2.id, t2.dashboard, t2.definition

See the demo.
Results:

>  id | dashboard      | definition  | url        
> --: | :------------- | :---------- | :----------
> 1.1 | Product,Cheif  | Overview    | http1,http2
> 2.1 | Cheif          | malpractice | http2      
> 3.1 | Client,Product | Vanity      | http3,http1

Split and Join in Where clause MySql

1 Answers1