Query to select several Categories at Once

[LoonyOnTheBus]

I have three tables:- items, category & itemscategory
Each item can have several categories associated with it.

Currently I have a query which allows you to select one category, which will output all items associated with that category.
What I’m trying to achieve is to be able to select several categories at once

So far I have come up with

SELECT itemname, COUNT(categoryid) AS Total
FROM items LEFT JOIN itemscategory
ON items.id = itemsid
WHERE categoryid IN ('1', '2', '3', '4')
GROUP BY items.id
HAVING Total = '4'

This query appears to work, but before I attempt to incorporate it into my PHP Code, I would just like some confirmation that it’s the best solution to the problem.

Many Thanks

[r937]

I would just like some confirmation that it’s the best solution to the problem.

you neglected to elaborate what the actual problem is

that query will return only those items that belong to all categories selected

by the way, please don't put quotes around numbers that are to be compared against numeric columns

[LoonyOnTheBus]

Sorry if I was unclear, will attempt to expand.

Each item in a table can have one or more categories assigned to it.
At the moment I have a query which can search on one choosen category, the query outputs all items containing that category.
The query looks like

SELECT itemname
FROM items INNER JOIN itemscategory
ON items.id = itemsid
WHERE categoryid = '$categoryid'

What I'm trying to achieve is the ability to search for more then one category at a time. So the query will only return items that contain the choosen categories.

Hope that helps.

[r937]

that's what i thought you meant

the query you gave in post #1 does, as you said, produce the expected results

and yes, it is the best solution to the problem

[LoonyOnTheBus]

That's great,
Thanks for the confirmation.

I can now look to include the query into my PHP Code.