Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Impossible many-to-many query?

    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
    FROM p
    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,
    Svad

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Welcome here.

    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
    http://www.mysql.com/doc/en/Fulltext_Search.html

  • #3
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?).

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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.

  • #5
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •