MySQL ODBC Connector - searching in an 'AND' style of query

Author

searching in an 'AND' style of query

starmonkey

2006-02-28, 8:28 pm

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)"

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/mysql? unsub...sie.nctu.edu.tw

Chris

2006-02-28, 8:28 pm

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/mysql? unsub...sie.nctu.edu.tw

starmonkey

2006-02-28, 8:28 pm

Quoting Chris < listschris@leftbrain
ed.org>:
> 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(*)

Thanks, Chris - that did the trick alright! I use PHP to make the query
dynamic, and now I can "drill down" into my content by selecting
categories in an AND fashion.

cheers!
sm

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links