1
-- Sample data, borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) NOT NULL auto_increment,
  `groupname` varchar(100),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `sm` (
  `id` int(6) NOT NULL auto_increment,
  `code` varchar(100),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`groupname`) VALUES
  ('100:2000'),
  ('100:2001'),
  ('101:2000'),
  ('101:2001'),
  ('102:2002'),
  ('103:2003');
INSERT INTO `sm` (`code`) VALUES
  ('100'),
  ('101'),
  ('2000');

SQL Fiddle of same: http://sqlfiddle.com/#!9/54714e/39

In MySQL, I would like to filter only the group names that contains various combinations of values in the sm table. i.e. 100,101,2000.

The below query returns other combinations too like 

100:2000
100:2001
101:2000
101:2001

SQL:-

select distinct d.groupname from docs d, sm s  where d.groupname like concat(s.code,'%') or d.groupname like concat('%',s.code);

Original question:-

mysql query to find all possible like combinations start with and ends with

Update:-

Expected results:-

100:2000
101:2000
Jay
  • 9,189
  • 12
  • 56
  • 96

1 Answers1

1

I've just broke your or condition into make multiple join with the same table like below to achieve your expected result,

select distinct d.groupname from docs d
join sm s1 on
d.groupname like concat(s1.code,'%')
join sm s2 on
d.groupname like concat('%',s2.code);
Tamilvanan
  • 708
  • 1
  • 7
  • 21