Assign product category by string match from other table

Question

I have a lots of products that we need to assign categories and a table, that contains keywords by which you can identify what goes where. There may be multiple keywords for one category, in this case the first match from top should be used.

I came up with this test case, which works as I need:

CREATE TABLE Products
    (`name` varchar(30), `category_id` int, `filter` varchar (30))
;

INSERT INTO Products
    (`name`)
VALUES
    ('Plate'),
    ('Mushrooms'),
    ('Mushroom soup in a plate'),
    ('Mushroom on a plate'),
    ('Tomato soup'),
    ('Sausage'),
    ('Soupcan')
;

CREATE TABLE Filters
(`filter` varchar(30), `category_name` varchar(30))
;
INSERT INTO Filters
    (`filter`, `category_name`)
VALUES
('Soup', 'Meals'),
('Mushroom', 'Ingredients'),
('Plate', 'Containers')
;

CREATE TABLE Categories
(`id` int, `name` varchar(30))
;
INSERT INTO Categories
    (`id`, `name`)
VALUES
    (1, "Ingredients"),
    (2, 'Containers'),
    (3, 'Meals')
;

update Products
left join Filters
on Products.name LIKE CONCAT('%',Filters.filter,'%')
join Categories
on Filters.category_name = Categories.name
set Products.category_id = Categories.id, Products.filter = Filters.filter;

select Products.*, Categories.name as category_name from Products
left join Categories on Products.category_id = Categories.id

Category gets assigned by first match in "filters", just as I need.
But if I add another row to Filter table that has same category_id (for example try adding: ('Soupcan', 'Containers') to Filters), the results will not be the first match (or the last).
"Mushroom soup in a plate" must go to "Meals", because it matches first filter row "Soup".
But if I add 'Soupcan', 'Containers' row to Filters - "Mushroom soup in a plate" will now match "Mushroom" and get into "Ingredients" category.
I think the problem lies in sorting between first and second join, but I can't figure it out.

If you know a better approach to this task - feel free to offer. I just need to sort products by keyword matching, where topmost keyword matched will have the priority.

SQLFiddle

WHICH DBMS?????? SQL is a language. It's useless if we post solution to the wrong dbms. Below is a reference to SQL Server. Is that useful for you? — Eric, Nov 01 '17 at 18:57

score 1 · Accepted Answer · answered Nov 01 '17 at 18:53

1

I don't know what kind of SQL you are using. But what you are looking for is a join of a subselect of the Filter table.

See these links for more info:

SQL Server: How to Join to first row

LEFT JOIN only first row

answered Nov 01 '17 at 18:53

promicro

1,280
7
14

Seems like it. I'll try it tomorrow and tell you how it went. – Trost Nov 01 '17 at 20:56
Coudn't get it to work with my case :( Getting "You have an error in your SQL syntax;" and no idea how to implement it correctly. – Trost Nov 06 '17 at 15:39
"GROUP BY Products.name;" gives me the desired result, but it only works after "select" for me, and not after "update", between two "left join" (MySQL 5.6). I guess I better simplify\refine my question and post it again. – Trost Nov 06 '17 at 16:03
Created more general question and example, because I can't get anything working using your suggestions :( https://stackoverflow.com/questions/47142215/mysql-update-rows-with-double-left-join-limiting-first-match – Trost Nov 06 '17 at 17:14

Assign product category by string match from other table

1 Answers1