CODEWITHSUNDEEP
mysqlsql
user1992653
user1992653

Reputation: 29

How to use WHERE IN in subquery with value from string column?

I have a problem to use WHERE IN with a column value

I have data like below

Table Service

id  name            sd_ids
1   House Cleaning  1,2

Table Service Detail

id  name
1   living room
2   bedroom

Schema

CREATE TABLE IF NOT EXISTS `service` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(200) NOT NULL,
  `sd_ids` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `service` (`id`, `name`,`sd_ids`) VALUES
 ('1','House Cleaning','1,2');

 CREATE TABLE IF NOT EXISTS `service_detail` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `service_detail` (`id`, `name`) VALUES
('1','living room'),
 ('2','bedroom');

I already tried this

SELECT *,
  (
   SELECT 
     GROUP_CONCAT(name) 
   from service_detail 
     where id in(service.sd_ids)
  ) as service_detail
FROM service;

but the result it's not what I want

id  name            sd_ids  service_detail
1   House Cleaning  1,2     living

I put schema and my testing here http://sqlfiddle.com/#!9/49c34e/7

What I want to achieve to shows the result like below

id  name            sd_ids  service_detail
1   House Cleaning  1,2     living room,bedroom

I know this schema is not best practice, but I need suggestion to achieve that.

thanks

Upvotes: 0

Views: 40

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You may join using FIND_IN_SET in the join criteria:

SELECT
    s.id,
    s.name,
    s.sd_ids,
    GROUP_CONCAT(sd.name ORDER BY sd.id) AS service_detail
FROM service s
INNER JOIN service_detail sd
    ON FIND_IN_SET(sd.id, s.sd_ids) > 0
GROUP BY
    s.id,
    s.name,
    s.sd_ids;

enter image description here

Demo

In general you should avoid storing CSV data in your SQL tables. CSV data represents unnormalized data, and is difficult to work with (q.v. the above query). Instead, break every service ID onto a separate record for best results.

Upvotes: 1

Related Questions