MySQL query with GROUP BY and HAVING all search values in aggregate

Question

I've got three tables.

Table 1 is packages. Table 2 is package_to_keyword. Table 3 is keywords.

Packages can be connected to multiple keywords. So if I query package_to_keyword Joining keywords and packages I can search for all packages that relate to a certain keyword. That's the part I understand. enter image description here

NOW... my question is, how do I retrieve packages that match a LIST of keywords? Right now, in php, I loop a sql statement and then loop through all the results for each keyword and do an array_intersect() to filter down to the packages that show up in all the result sets. However, I'm sure this is bad, because I'm running a query for each keyword when I'm certain SQL is built to handle this type of relationship, I'm just not sure what type of query to perform.

The key is the list of keywords can be any number of words. If I use something like IN ('keyword','array','returns','all','results') I just get a list for all the packages that have a relationship with ANY of the keywords when I just want packages that have a relationship with ALL of the keywords.

Thoughts?

Clodoaldo Neto · Accepted Answer · 2012-05-31 17:29:09Z

3

select title
from packages p
inner join pack_to_tag pt on p.index = pt.pIndex
inner join keyworks w on w.index = pt.kindex
where word in ('keyword','array','returns','all','results')
group by title
having count(*) = 5

edited May 31, 2012 at 17:29

answered May 31, 2012 at 17:21

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

DRapp · Accepted Answer · 2012-05-31 17:29:00Z

2

First, the "PreQuery" (qualified products result) looks only the products joined to keywords that have ANY of the keywords you are looking for, and will ultimately return 1 or more entries. The GROUP BY then confirms however many you ARE EXPECTING... Then join to products for final results.

select
      p.*
   from
      ( select ptt.pIndex
           from pack_to_tag ptt
                   join keywords k
                      on ptt.kindex = k.index
                     and k.word in ( 'word1', 'word2', 'word3' )
           group by 
              ptt.pIndex
           having 
              count(*) = 3 ) QualifiedProducts
      join Products p
         on QualifiedProducts.pIndex = p.index

answered May 31, 2012 at 17:29

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Collectives™ on Stack Overflow

MySQL query with GROUP BY and HAVING all search values in aggregate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related