sql - How can i compose this MySQL query? -

the user enters 1 6 words (characteristics) input data 6 words. here table:

---------------- | user |  word | ----------------    1   |  green    1   |  black    1   |  red    2   |  green    2   |  gray 

for example, array consists of 2 words: "green", "black". how can receive "user=1" if amount of conjunctions in it's characteristics maximal. i've tried query:

// $pat - array of words // $nums - minimal amount of conjunctions. $nums = 0.8*count($pat)  select user words word in ('{$pat[0]}', '{$pat[1]}','{$pat[2]}','{$pat[3]}','{$pat[4]}','{$pat[5]}') group `user` having count(*) >= ".$nums." order count(*) desc limit 1 

the problem condition having count(*) >= ".$nums." doesn't work , data extracted if there 1 conjunction.

i believe query working fine

in case when have 2 words 'green' , 'black'

select user   words  word in ('green', 'black')  group user having count(*) >= (0.8 * 2)  order count(*) desc  limit 1; 

as expected user 1 have have both words (which more 0.8 * 2):

 | user | -------- |    1 | 

now if have 5 words

select user   words  word in ('green', 'black', 'red', 'green', 'gray')  group user having count(*) >= (0.8 * 5)  order count(*) desc  limit 1; 

the result set empty (again expected) since there no user has @ least 4 (0.8 * 5) words.

here sqlfiddle demo

therefore seems you're not telling or real data different

---

1 of probable causes might existence of duplicate rows same word same user. if it's case can either enforce restriction unique constraint on table or using count(distinct word)

select user   words  word in ('green', 'black')  group user having count(distinct word) >= (0.8 * 2)  order count(distinct word) desc 

here sqlfiddle demo