[Spode]

Hi Guys,

Fairly simple scenario, but short of having to do some PHP work and then make a second query, here is the problem.

I have a "link table" as I like to call it, which has category_id,video_id as fields. The idea being so I can give a video more than one assocated category_id.

So I search the table, for all videos, where the category_id is X. However, I'd like to nest the categories. For instance, I want a video that's category is X AND X.

Am I going to have to do a query if the category is X OR X and then use PHP to ween out the ones that aren't in both? Or can I do this in SQL?

More to the point, which would be less resource hungry?

[Catalyst]

SQL is going to give you better performance. While this isn't the only way to do it I'd say what you want is to INTERSECT two queries, this gives you the results that are in both of them.

Generally it would by:

SELECT video_id FROM linktable WHERE category_id=12
INTERSECT
SELECT video_id FROM linktable WHERE category_id=19

But, MySQL doesn't support INTERSECT. Fortunately you can do the same thing using a self join:

SELECT A.video_id
FROM linktable A INNER JOIN linktable B
ON A.video_id=B.video_id
WHERE A.category_id=12 AND B.category_id= 19

[Catalyst]

Here's another way to do it that doesn't use INTERSECT and might be easier if you have more than 2 categories you're trying to match (I'll use 5 in this example)

SELECT video_id
FROM linktable
WHERE category_id IN (3,7,9,11,18) <-- Comma'd list of categories
group by video_id
HAVING count(video_id)=5 <-- Number of items in the comma'd list

[Spode]

Once again proving that my knowledge of SQL is definetely a weak point for me

I'll look in to those now, but I have a strong suspicion that should work nicely.

[Ben Abrams]

yeah they seem quality solutions, they should work perfectly

[Spode]

Yup, got it working. I had a bit of a search around some of the stuff in those queries and it really amazes me how powerful MySQL is.

However, what I did in the end, was basically what you suggested, GROUP BY, and then HAVING count(video_id) > x. Lovely

[Catalyst]

Great. That was definitely the more flexible of the two ways.