CODEWITHSUNDEEP
mysqlsqldatabase
Nobody One
Nobody One

Reputation: 213

How can i compose this MySQL query?

The user enters from 1 to 6 words (characteristics) so the all input data is 6 words. Here is my table:

----------------
| USER |  WORD |
----------------
   1   |  Green
   1   |  Black
   1   |  Red
   2   |  Green
   2   |  Gray

For example, array consists of 2 words: "Green", "Black". How can I receive "User=1" if the amount of conjunctions in it's characteristics is maximal. I've already tried this query:

// $pat - array of words
// $nums - minimal amount of conjunctions.
$nums = 0.8*count($pat)

SELECT user
FROM words
WHERE word IN ('{$pat[0]}', '{$pat[1]}','{$pat[2]}','{$pat[3]}','{$pat[4]}','{$pat[5]}')
GROUP BY `user`
HAVING count(*) >= ".$nums."
ORDER BY count(*) DESC
LIMIT 1

The problem is that condition HAVING count(*) >= ".$nums." doesn't work properly and the data is extracted even if there is only one conjunction.

Upvotes: 2

Views: 133

Answers (1)

peterm
peterm

Reputation: 92845

I believe your query is working just fine

In case when we have two words 'Green' and 'Black'

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black')
 GROUP BY user
HAVING COUNT(*) >= (0.8 * 2)
 ORDER BY COUNT(*) DESC
 LIMIT 1;

As expected only user 1 have have both words (which is more then 0.8 * 2):

| USER |
--------
|    1 |

Now if we have 5 words

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black', 'Red', 'Green', 'Gray')
 GROUP BY user
HAVING COUNT(*) >= (0.8 * 5)
 ORDER BY COUNT(*) DESC
 LIMIT 1;

The result set is empty (again as expected) since there is no user who has at least four (0.8 * 5) words.

Here is SQLFiddle demo

Therefore it seems that you're not telling us something or you real data is probably different

One of the probable causes might be existence of duplicate rows with the same word for the same user. If it's the case you can either enforce a restriction with UNIQUE constraint on your table or using COUNT(DISTINCT word)

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black')
 GROUP BY user
HAVING COUNT(DISTINCT word) >= (0.8 * 2)
 ORDER BY COUNT(DISTINCT word) DESC

Here is SQLFiddle demo

Upvotes: 3

Related Questions