Serendipity Forums

Posted: **Mon Apr 11, 2005 7:37 pm**

If I have several categories, say they are named AAA, BBB, CCC, ..., ZZZ, and I assign my posts to multiple categories like this:

Post 1 is in category AAA, BBB, and CCC.
Post 2 is in category AAA and CCC.
Post 3 is in category AAA, BBB, CCC and DDD.

Is it possible to set up a query that will return all posts that only fall into ALL of the selected categories (A logical AND)? For example, searching in category AAA only will return all three posts. Searching in AAA AND BBB will return posts 1 and 3. AAA and DDD will return only post 3.

Thanks,
Scott

Posted: **Mon Apr 11, 2005 7:59 pm**

This solution comes instantly to my mind:

Code: Select all

SELECT e.id, e.title, c1.categoryid, c2.categoryid
FROM serendipity_MERGE_entries AS e
LEFT OUTER JOIN serendipity_MERGE_entrycat AS ec1 ON e.id = ec1.entryid
LEFT OUTER JOIN serendipity_MERGE_entrycat AS ec2 ON e.id = ec2.entryid
LEFT OUTER JOIN serendipity_MERGE_category AS c1 ON ec1.categoryid = c1.categoryid
LEFT OUTER JOIN serendipity_MERGE_category AS c2 ON ec2.categoryid = c2.categoryid
WHERE c1.categoryid =1 AND c2.categoryid = 2
ORDER BY e.id DESC

This means, you have to create n joins to the entrycat+category tables for the amount of "AND" conditions you are going to have.

You can of course also do the WHERE matching on c1/c2.category_name instead.

HTH,
Garvin

Posted: **Mon Apr 11, 2005 8:24 pm**

Wow, that was quick!

I'm new to SQL and PHP, but I'll try to get that put in over the next few days and let you know how it works.

Great program!

Thanks again,
Scott

Serendipity Forums

Selecting cross-referenced posts.

Selecting cross-referenced posts.

Re: Selecting cross-referenced posts.

RE: Selecting cross-referenced posts.