starmonkey wrote:
q: I want to pull a distinct list of "content" that have a "category"
(we have a content_categories table linking content ids with category
ids), BUT I want it to be able to work from multiple categories in an
AND fashion.
the table structure is, roughly:
content:
id
name
blahblahblah
content_categories:
content_id
category_id
categories:
id
name
blahblahblah
Eg:
"give me all content with categories animal (id 3) AND vegetable (id
5) AND mineral (id 9)"
This could be done something like this, there *might* be a better way,
but this seems pretty clean to me:
SELECT content.name
FROM content as c
INNER JOIN content_categories as cg
ON(c.id = cg.content_id)
INNER JOIN categories as g
ON(cg.category_id = g.id)
WHERE g.id IN (3,5,9)
GROUP BY c.id
HAVING 3 = COUNT(*)
I didn't test this, but the idea is to group the content by content name
and count how many of the 3,5,9 categories it has.
Anything that matches all three, provided there are no dulicate rows
incontent_categories, should have a COUNT(*) of 3 rows.
If it's an OR process, it's easy -
SELECT DISTINCT blah blah ... AND c.catid in (10,9,23,11)
- but that's effectively an OR search, which is not what I want.
note: please cc me on list replies, cause I'm on the digest!
thanks,
sm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
