I'm setting up a kind of search engine for products. The basic schema is as follows:
products table: p - id, name
keyword table: k - id, word
junction table: j - pid, kid (product id and keyword id, respectively)
Need to find products with keyword "cats"? No problem!
SELECT DISTINCT p.name
INNER JOIN ( k
INNER JOIN j
ON ( k.id = j.kid )
AND ( k.word = 'cats' )
) ON p.id = j.pid
Need to find products with keywords "cats" or "books"? Just add "OR ( k.word = 'books' )" after the 'cats' condition. Again, no problem.
Now, what if I want to find products that are books about cats? That is, I need a query that will only return products associated with *both* keywords (or any number really). I can't figure it out. There must be some elegant solution.
Thanks for your help,
count the number of records from the junction table, grouped by 'pid', if this number is the same as the number of keywords you're looking for, then this is one of the products you need. To get the productsinfo in one query, you'll need to use a subselect (available from MySQL version 4.0 on).
But i hink it would be better to store the keywords in a variable in the productstable, and use the full text search function
Thanks for the welcome.
Counting the records from the junction table was something I tried, but wasn't sure how to implement correctly. At the end of the query example I gave, I tried adding "HAVING COUNT (j.pid) = 2". But it didn't work as planned. And I'm not sure where I'd stick the GROUP BY clause. Could you or someone provide an example?
I will definitely take a look at the full text search function. Though my entire search schema is more complex and involves keyword hierarchies and a thesaurus (getting in a little over my head for an SQL newbie?).
example of a count
sql="SELECT Count(*) as countrec FROM junction GROUP BY pid WHERE kid LIKE '%cats%' or kid LIKE '%book%' ORDER BY countrec desc"
The count(*) as countrec means all records that meet the condition (where clause) are counted. Countrec will be the alias --> the column name in your recordset. LIKE '%cats%' means that the keyword can be something with cats in it (only necessary if the user can enter keywords, else use the = operator without wildcards)
This query will return a recordset with all pid's and there count. By using Having, you can limit to only the desired records.
If you need some product-info with it, then join with the products table
sql="SELECT productstable.name, Count(*) as countrec FROM junction INNER JOIN productstable ON junction.pid=productstable.pid GROUP BY pid WHERE kid LIKE '%cats%' or kid LIKE '%book%' HAVING countrec=$numberofkeywords "
$numberofkeywords is a variable that holds the number of keywords from the where clause.
Not really an everyday querys so you might need some experimenting, but its doable. Just let us know if you run into problems.
This seems to work:
SELECT DISTINCT p.name
FROM p INNER JOIN
( k INNER JOIN j ON ( ( k.id = j.kid )
AND ( ( k.word = 'books' ) OR ( k.word = 'cats' ) ) )
) ON ( p.id = j.pid )
GROUP BY p.id HAVING COUNT( j.pid ) = 2
Tried it with other combinations, and it continued to work. Of course, this only allows logical 'and' functionality, but that's probably all this search needs to support.
Thank you for your help. I'll consider your other examples for their uses.