CODEWITHSUNDEEP
mysqlsql
Radu Andrei
Radu Andrei

Reputation: 1073

sql set type match

I have a table articles with a column being of type SET named categories. Let's presume it accepts values from cat1, cat2, cat3, cat4 set. Here's a visual representation:

articleTitle | categories (of type SET) | ID
--------------------------------------------
Socializing  | cat1                     | 1
Programming  | cat1,cat2                | 2
Sports       | cat1,cat2,cat3           | 3
Hiking       | cat2                     | 4
Nutrition    | cat1                     | 5
Health       | cat1,cat2,cat4           | 6

1. I want to do an SQL select to return the article name of the first n rows where the val1 parameter matches any of the values from the categories column. eg: if val1=cat1 then the results would be Socializing, Programming, Sports, Nutrition, Health;

2. I also want to do an SQL select query to determine if both val1, val2 match two values from the categories column. eg: if val1=cat1 AND val2=cat2 then the results would be Programming, Sports, Health;

3. Lastly, it would be ideal to do a query that returns the first n rows of articles with the highest number of matches in the categories column - meaning total matches supersedes partial matches and first match from list of values takes priority over second one. eg: if val1=cat1 AND val2=cat2 AND val3=cat3 AND numerOfRows=4 then the results would be Sports, Programming, Health, Socializing;

How would i go about doing this? Is it even possible or do i need to run multiple queries - i guess int he multiple queries scenario the counting and matching logic would be left to the back-end side of things and i'd do one thing at a time ? Would love to get help first two scenarios still.

If there's requirement for clarification, ask and i'll amend the question.

Upvotes: 0

Views: 90

Answers (1)

Philip Devine
Philip Devine

Reputation: 1169

Edited after rereading the question, it's correct now. SQL fiddle below: http://sqlfiddle.com/#!9/65c5b/7

Question 1: 

SELECT * FROM articles WHERE categories like '%a%' LIMIT 0, 5;

Question 2:

SELECT * FROM articles WHERE categories like '%a,b%';

Question 3:

SELECT * FROM articles WHERE categories LIKE '%a%' or 
categories LIKE '%b%' or
categories LIKE '%c%' or
categories LIKE '%d%'
order by FIND_IN_SET('a',categories )
 + FIND_IN_SET('b',categories ) 
 + FIND_IN_SET('c',categories )
 + FIND_IN_SET('d',categories ) desc
LIMIT 0, 5;

I omitted the acutal CONCAT function from the variables because I don't know if this is in php, sql, or some other app, but I think it's definitely enough to get the jist of it. Let me know if it's not.

Upvotes: 1

Related Questions