Mysql query 1 Table same column multiple times

Question

I'm going crazy on this hope someone can help me out....

I have one Table:

user_skils:
ID  | UID | SKILL
1     23    House
2     5    Disco
3     8    Punk
...  ...    ...
...  ...    ...
...  ...    ...

Now Im building a search query where the user can search and filter out people that don't match the criteria:

Example Search: Disco, Punk, House

Meaning that I only want the users that match this 3 criterias ( have House AND Disco AND PUNK)... How can I manage this via a query?

Something like

SELECT count(uid) as matches , 
  GROUP_CONCAT(skill) as skills_grouped 
FROM user_skilks 
WHERE skill LIKE %Disco% 
  AND skill LIKE %punk% 
  AND skill LIKE %house%

Should give me something like:

Matches | skills_grouped
3         House,Punk,Disco

Meaning that 3 people match this criteria...

GROUP BY ... HAVING COUNT(*) = 3 -- and why are you using '%'. Just use IN('Disco','Punk','House') — Strawberry
– Strawberry, Commented Jan 16, 2013 at 23:17
I would use an selection list to enter the skils. Otherwise you will run in to trouble when an user input is not exactly the same as your query. For example, "punk" would not match with " punk", etc. — Mr. Radical
– Mr. Radical, Commented Jan 16, 2013 at 23:23
@Strawberry my mistake MySQL is not case sensitive by default. In the case that the data is used in for example PHP I would recommend still recommend it. — Mr. Radical
– Mr. Radical, Commented Jan 16, 2013 at 23:34

eggyal · Accepted Answer · 2013-01-16 23:31:57Z

1

Group your table by UID and then filter the resulting groups (i.e. using the HAVING clause) for those of interest:

SELECT   UID
FROM     user_skils
GROUP BY UID
HAVING   SUM(SKILL LIKE '%House%')
     AND SUM(SKILL LIKE '%Disco%')
     AND SUM(SKILL LIKE '%Punk%' )

This works in MySQL because it does not have true boolean types. In other RDBMS, you would have to use a CASE expression:

HAVING   SUM(CASE WHEN SKILL LIKE '%House%' THEN 1 ELSE 0 END) > 0
     AND ...

To get the number of such users, group the results again:

SELECT COUNT(*) FROM (
  SELECT   1
  FROM     user_skils
  GROUP BY UID
  HAVING   SUM(SKILL LIKE '%House%')
       AND SUM(SKILL LIKE '%Disco%')
       AND SUM(SKILL LIKE '%Punk%' )
) t

edited Jan 16, 2013 at 23:31

answered Jan 16, 2013 at 23:26

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query 1 Table same column multiple times

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related