CODEWITHSUNDEEP
phpmysqlsqlarrays
George
George

Reputation: 325

Select foreign key (group) where is the biggest match

I have three tables group_sentences, group_sentences_attributes and group_senteces_categories.

I have an attributes array which I am using in query with IN (after implode).

Then I have one category ID because they are stored recursively, so no need for an array.

I need to select one group number where is the biggest match for $attributesArray and of course category too.

Here is table group_sentences_attributes

+-----+-------+-----------+
| id  | group | attribute |
+-----+-------+-----------+
|  1  |   1   |   3564    |
|  2  |   1   |   3687    |
|  3  |   1   |   3689    |
|  4  |   2   |   3687    |
|  5  |   2   |   3564    |
+-----+-------+-----------+

Here is group_sentences_category

+-----+-------+----------+
| id  | group | category |
+-----+-------+----------+
|  1  |   1   |   1564   |
|  2  |   1   |   1221   |
|  3  |   1   |   1756   |
|  4  |   2   |   1358   |
|  5  |   2   |   1125   |
+-----+-------+----------+

Here is my query, but I am afraid that it won't do the job done.

SELECT group_categories.group
FROM group_categories, group_attributes 
WHERE group_categories.category = '$category'
    AND group_attributes.attribute IN ($attributesArray)
GROUP BY group_categories.group
ORDER BY count(group_attributes.attribute)

Any help would be appreciated, thanks.

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

First, the table in your query do not match the tables in the question. I am guessing they are simply missing the "sentence". Then, you have no join clause. Simple rule: Never use commas in the from clause.

group is a lousy name for a column, because it is a keyword in SQL. The following may be what you are looking for:

SELECT gc.groupid
FROM group_sentences_attributes sa JOIN
     group_sentences_category sc
     ON sa.groupid = sc.groupid
WHERE sc.category = '$category' AND
      sa.attribute IN ($attributesArray)
GROUP BY sa.groupid
ORDER BY count(sa.attribute);

If you only want one row, then add LIMIT 1 to the end.

Upvotes: 1

Related Questions