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.

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 :

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.

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?

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.

Sorry for being a dunce but if I have categories xxxx and yyyy where would they go in your query?

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.

There was an error in your syntax...let's leave it until tomorrow. Thanks, 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 )
               );

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...

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.

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?

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.

I think that's fast enough

Many thanks for all your help. Have another glass of beer.