Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Tricky query

  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Unanswered: Tricky query

    I am struggling to find a way to create the results I want. I wonder if any expect out there would be able to help. Using mysql 5.0.

    I have three tables, let's simplfy things and call them 'product', 'category' and 'xref'.
    'product' table contains information about products and has two fields: product_id, product_description
    'category' table has two fields: category_id and category_name. It links products to categories, a product usually exists in more than one category so there are multiple entries with the same product_id and different category_id
    'xref' table has two fields: category_id and product_id and is a
    one-to-one match of category_id to category_name

    The query I want to creatre is teh result of specifying search options. The searcher specifies a number of category names and the aim is to retrieve the product_id and product_description for all products that exist in all of the categories specified in the search criteria.

    Any suggestions? It doesn't seem easy to me...
    Many thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would it be a single string containing all the category names?
    Could it contain product names?
    Can you supply a few example searches?
    How many records in each table roughly?

    I assume a product can exist in multiple category and that's why the xref table is there.

    Mike

    If you're entering the search string as "category name 1, category name 2" then the following might work (difficult to test without tables or data) but it's a first attempt. You'll need to put your search into the two bits called search_str below :
    Code:
    select p.product_id, p.product_description
    from   product p, xref x, category c
    where  c.category_id = (
                 select min( category_id )
                 from   category c
                 where  concat( ',' , 'search_str' , ',' ) like 
                           concat( '%,' , c.category_name , ',%' )
           and x.category_id = c.category_id
           and p.product_id = x.product_id
           and not exists(
                 select 1
                 from   xref x2, category c2
                 where  x2.product_id = p.product_id
                        and c2.category_id = x2.category_id
                        and concat( ',' , 'search_str' , ',' ) not like 
                              concat( '%,' , c.category_name , ',%' )
                   )
    Last edited by mike_bike_kite; 02-21-09 at 17:47.

  3. #3
    Join Date
    Feb 2009
    Posts
    12
    The category names are taken from separate fields. They could be put into an array I suppose.
    There are several dozen categories, a few hundred products and each product is in five categories.
    An example search would be:

    Say there are the following products:
    product_id and category_name are the following
    23 xxxx
    24 xxxx
    25 xxxx
    23 yyyy
    26 yyyy
    27 yyyy
    28 zzzz

    I specify a search for products in categories xxxx and yyyy. The only result I want back is 23 as is in both categories specified.

  4. #4
    Join Date
    Feb 2009
    Posts
    12
    This was something I have managed to come up with.

    select product_id from
    (SELECT x.product_id, c.category_name
    FROM xref x
    INNER JOIN category c ON c.category_id = x.category_id
    WHERE c.category_name in ('xxxx','yyyy'))Z
    group by product_id
    having count(distinct category_name)=(select count(distinct category_name) from category)

    The syntax may not be quite right though for mysql as it returns no results.

    select product_id from
    (SELECT x.product_id, c.category_name
    FROM xref x
    INNER JOIN category c ON c.category_id = x.category_id
    WHERE c.category_name in ('xxxx','yyyy'))Z
    group by product_id
    having count(distinct category_name)=2

    This does work though...in this case I have replaced the last 'select' with 2 onbly because I specified two categories in the where clause.

    Does that help?

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can you try the SQL I gave above and see if it produces the correct results. It's not that efficient but I think it should be fast enough for what you want (if it works!). Sorry for lack of INNER JOINS etc - I find it difficult thinking that way.

  6. #6
    Join Date
    Feb 2009
    Posts
    12
    Sorry for being a dunce but if I have categories xxxx and yyyy where would they go in your query?

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    They would go in the place where it says "search_str" -> "xxxx,yyyy"

    However I can see I've missied some thing and I want to have a beer and watch a film now - I'll try to redo the code in the morning (UK) if no-one has provided an answer by then.

  8. #8
    Join Date
    Feb 2009
    Posts
    12
    There was an error in your syntax...let's leave it until tomorrow. Thanks, enjoy your beer.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    enjoy your beer
    I did and oddly enough thought out what was wrong. This should work (I said that last time) and I think it will be fast enough for the amount of data you have. You'll want to swap the xxxx,yyyy for a variable containing the category names. If it matters to you then you can add the proper JOIN clauses later :
    Code:
    select p.product_id, p.product_name
    from   product p, xref x, category c
    where  c.category_id = (
                 select min( category_id )
                 from   category c
                 where  concat( ',' , 'xxxx,yyyy' , ',' ) like
                           concat( '%,' , c.category_name , ',%' ) )
           and x.category_id = c.category_id
           and p.product_id = x.product_id
           and not exists(
                 select 1
                 from   category c2
                 where  concat( ',' , 'xxxx,yyyy' , ',' ) like
                              concat( '%,' , c2.category_name , ',%' )
                        and not exists(
                            select  1
                            from    xref x2
                            where   x2.product_id = p.product_id
                                    and x2.category_id = c2.category_id )
                   );

  10. #10
    Join Date
    Feb 2009
    Posts
    12
    Well, that beer must have refreshed your grey cells. Your query seems to work!
    Many thanks. I wonder if it can be optimised in some way...

  11. #11
    Join Date
    Feb 2009
    Posts
    12
    On second thoughts, it works. I am not sure how it works yet, but it does. And I shouldn't really fix something that isn't broke. I'll keep an eye on this forum though just in case another clever person comes along with a different method.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I wonder if it can be optimised in some way...
    How long does it take to run at the moment with the data you have?
    How much do you expect your data to grow?
    How fast do you need the query to run?

  13. #13
    Join Date
    Feb 2009
    Posts
    12
    I have got 28 rows in category and xref, and only 4 products.
    Through phpmyadmin, searching with two category names takes only 0.0013 seconds. Not a lot I suppose, perhaps I should leave things as they are.

    I expect this will grow to severl dozen entries in category and xref and several hundred products.

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    0.0013 seconds
    I think that's fast enough

  15. #15
    Join Date
    Feb 2009
    Posts
    12
    Many thanks for all your help. Have another glass of beer.

Posting Permissions

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