SQL Taxonomy Help

I have a database that relates content by taxonomy and I am trying to query that content by taxonomy. It looks like this:

Table 1

content_id, content_name

Table 2

content_id, content_taxonmoy

What I am trying in my query is to find content with two or more types of related taxonomy. My query looks like this:

SELECT content_id FROM table_1 JOIN table_2 ON table_1.content_id=table_2.content_id WHERE content_taxonomy='ABC' AND content_taxonomy='123'

Except it returns nothing. I later t开发者_运维技巧ried a group by with:

SELECT content_id FROM table_1 JOIN table_2 ON table_1.content_id=table_2.content_id WHERE content_taxonomy='ABC' AND content_taxonomy='123'GROUP BY content_id, content_taxonomy

But that didn't work either. Any suggestions please?

SELECT  *
FROM    content c
WHERE   (
        SELECT  COUNT(*)
        FROM    taxonomy t
        WHERE   t.content_id = c.content_id
                AND t.content_taxonomy IN ('ABC', '123')
        ) = 2

Create a UNIQUE INDEX or a PRIMARY KEY on taxonomy (content_id, content_taxonomy) for this to work fast.

SELECT  c.*
FROM    (
        SELECT  content_id
        FROM    taxonomy
        WHERE   content_taxonomy IN ('ABC', '123')
        GROUP BY
                content_id
        HAVING  COUNT(*) = 2
        ) t
 JOIN   content c
 ON     c.content_id = t.content_id

In this case, create a UNIQUE INDEX or a PRIMARY KEY on taxonomy (content_taxonomy, content_id) (note the order or the fields).

Either solution can be more or less effective than another one, depending on how many taxonomies per content do you have and what is the probability of matching.