[RESOLVED] Comparing

Shawazi

I am curious if there is a command I'm unaware of in MySQL to perform a task that seems not too hard. Here are simplified versions of two tables I have.

Groups

name

Apples
Bananas
Oranges
Trees

Group Keywords

group name | keyword name

Apples Yummy
Apples Round
Bananas Yummy
Oranges Yummy
Oranges Round
Trees Tall

The goal is to have a query that will get related groups via the keywords. So if I am looking at Apples, it will be related to Bananas and Oranges because all of them link to at least Yummy. Oranges will come first because it matches both keywords, where Bananas only match one. Then Trees do not show at all.

PS: in reality I use the primary key IDs, but using just names made it easier.

Shawazi

As I brainstorm, if I did

WHERE keyword_name = "round" OR keyword_name = "yummy"

I know it would list both Apples and Bananas, but is there another parameter that knows which matched more?

NogDog

Assuming the ID field in each table is called "group_id":

SELECT g.name FROM groups g 
  INNER JOIN keywords k ON g.group_id = k.group_id 
  WHERE k.keyword_name = 'Yummy'
  ORDER BY g.name

Shawazi

If I do an INNER on two terms, it won't select ones without two terms. If I do a LEFT, it will show both, but then I do not know how to order them so that Oranges come higher than Banana

NogDog

OK, I think I misunderstood the original question. Let me think about it....

Shawazi

Yeah sorry I was not clear in saying I need to select and then order them by that criteria. I'm currently playing around with some queries and looking at the syntax for the MATCH function to see if I can place it in a subquery after ORDER BY in some manner.

NogDog

OK, this one set my head spinning a bit, but I think this might be at least close to what you need:

SELECT g.name, COUNT(k.keyword_name) AS ct
  FROM groups g, keywords k
  WHERE g.name = k.group_name AND
    k.keyword_name IN 
      (SELECT keyword_name FROM keywords WHERE group_name = 'Apple')
  GROUP BY g.name
  ORDER BY ct

Shawazi

If I use GROUP_CONCAT it will give a row for each Group and all of it's Keywords. I am pretty sure this is the right direction I need to go. However when I am testing in phpMyAdmin I get back [BLOB - 7 Bytes] in the value. Is there a way I can view the value without using PHP?

Shawazi

I'm gonna give your query a try.

Shawazi

it returned an empty set but i see where you're going with it.

Shawazi

works 🙂 just messed up somewhere earlier

SELECT g.id, g.name, COUNT(k.id) AS thisCount FROM
groups g, keywords k
WHERE g.id=k.group_id AND
k.term_id IN (SELECT ki.term_id FROM keywords ki WHERE ki.group_id=1)
GROUP BY g.id