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

Question

In Mysql I have a table like this

And I want to filter only those records that can either start with or ends with all the below possible combinations in the groupname column. (sm.code)

I tried a query but have some problem in finding all possible combinations.

SQL:-

select cr.groupname,cr.description,cr.higher_limit,cr.lower_limit, ce.severity from compatibility_rule as cr 
        join starting_material as sm on sm.id=cr.starting_material_id 
        join component as c on c.id=sm.component_id and c.cas_number in ('67-56-1','67-64-1')
        join compatibility_error as ce on ce.id=cr.compatibility_error_id 
        and (cr.groupname like concat('%: ', sm.code) and cr.groupname like concat(sm.code, ' : %'))  
        and c.active=true and sm.active=true and cr.active=true 
        order by cr.groupname;

I might have to tweek something in this part, but not sure how.

and (cr.groupname like concat('%: ', sm.code) and cr.groupname like concat(sm.code, ' : %'))

Update:- Also posted simplified question in here

I have now created a sample on SQLFiddle below link

SQL Fiddel: http://sqlfiddle.com/#!9/54714e/39

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

SQL:-

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

Could you put samples of the other tables you're using in your query? Anyway, as said by @PM77-1 the `or` should work fine. — Lucas, Jul 23 '19 at 14:44
Thanks, guys in fact the first screenshot was using 'OR' in the query. — Jay, Jul 23 '19 at 14:51
See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) ... Don't provide images.. — Raymond Nijland, Jul 23 '19 at 15:08
If it was me, I'd store the groupname prefix and suffix in separate columns. — Strawberry, Jul 23 '19 at 15:14

score 0 · Answer 1 · answered Jul 23 '19 at 17:35

0

based on the schema that appears in http://sqlfiddle.com/#!9/54714e/1 you can use this query

select distinct * from (select d.id , d.groupname from docs as d,sm as s where d.groupname like (CONCAT("%",s.code)) or (CONCAT(s.code,"%"))) as t;

answered Jul 23 '19 at 17:35

Mohammad Shrateh

74
4

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

1 Answers1

Linked