SQL, matching all tags

170
April 21, 2018, at 10:51 PM

I suppose that there is a really simple solution which I just can not see.

The case: I have a table containing products with different kind of details aka name, brand, manufacturer, category etc.

Then I have a table (which contains information about manuals with tags specifying matching criteries against to the product table. Tags can be separated to an another table if necessary. Manual matches the product only if all tags matches.

So, how to check which manuals would suit to the current product?

Example:

**Product 1:**
Name: P10
Brand: Huawei
Manufacturer: Huawei Co Ltd
Category: Mobile Phone
**Manual 1:**
Tags: Samsung, Galaxy, Mobile Phone
**Manual 2:**
Tags: Huawei, Mobile Phone, P9
**Manual 3:**
Tags: Huawei, Mobile Phone
**Manual 4:**
Tags: Huawei
**Manual 5:**
Tags: Huawei Co Ltd, Mobile Phone, P9

So, when looking which manuals would match the Product 1, I should get result containing Manuals 3, 4 and 5. But what kind of query structure would do that?

Answer 1

Try this:

SELECT A.id tag_id, A.tags, B.*
FROM Manual A JOIN Product B
ON (length(A.tags)-length(replace(A.tags ,',','')))+1=
     ((INSTR(A.tags,B.Name)>0) + 
     (INSTR(A.tags,B.Brand)>0) + 
     (INSTR(A.tags,B.Manufacturer)>0) + 
     (INSTR(A.tags,B.Category)>0));

See Demo on SQL Fiddle.

Rent Charter Buses Company
READ ALSO
Can we create Binary Tree in java using JDBC with all operations?

Can we create Binary Tree in java using JDBC with all operations?

Can we create Binary Tree in Java using JDBC with all operations like insert Node, search node, delete the node, sort tree etc?

131
sqlAlchemy execute a select row for the second time, it return nothing

sqlAlchemy execute a select row for the second time, it return nothing

I select and update a row data by sqlalchemy, when I execute the second time, sqlalchemy return None to me, why?

228
I want to insert 3 images into a folder and path into mysql with new name

I want to insert 3 images into a folder and path into mysql with new name

I want to insert 3 images into a folder and path into mysql with a new name

165
php msql rollback not rollback all change in loop

php msql rollback not rollback all change in loop

I tried to find why some changes were not rollback when some queries failedIt should be all rollback, but it rollback some

175