MySQL Join on two rows

I'd like to be able to get rows from a "articles" table based on two "categories" however i'm having trouble with my joins. Here's what my tables would look like:

`articles` (
  `article_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `article_name` varchar(255) NOT NULL
  PRIMARY KEY (`article_id`)
)

`article_categories` (
  `article_id` smallint(5) unsigned NOT NULL,
  `category_id` smallint(5) unsigned NOT NULL
  UNIQUE KEY `article_category_id` (`article_id`,`category_id`),
  KEY `article_id` (`article_id`),
  KEY `category_id` (`category_id`)
)

Now what i'd like to be able to do is get all articles which are in both categories 3 and 5 (or an unlimited number of categories). I thought I could do something like this:

SELECT * FROM articles a INNER JOIN article_categories ac ON ac.article_id = a.article_id WHERE (ac.category_id = 3 AND ac.category_id = 5)

Just to clarify I don't want articles that are in EITHER 3 or 5, but BOTH 3 AND 5.

I'm thinking that this is one of those really simple things that i've somehow missed due to tiredness or something.

Either that or I will literally have to do another join for every category that I want to include e.g:

SELECT a.* FROM articles a INNER JOIN article_categories ac ON ac.article_id = a.article_id INNER JOIN article_categories ac2 ON ac2.article_id = a.article_id WHERE (ac2.category_id = 3 AND 开发者_如何转开发ac.category_id = 5)

But i'm sure there's a simpler solution that that.

---

try

select 
  articles.article_id
from 
  articles, 
  article_categories
where 
  articles.article_id=article_categories.article_id and
  article_categories.category_id in(3,5)
group by
  article_categories.article_id
having count(*)>=2;

---

1) following query should work (I haven't test it)

select art.*
from
  articles art
  inner join cat1 on cat1.article_id = art.article_id
  inner join cat2 on cat2.article_id = art.article_id
where
  cat1.category_id = 3
  and cat2.category_id = 5

2) Your data model is wrong. You shoud have 3 tables: aticle, category and aticle_to_category, table names should not be in plurar form.

---

Another alternative would be:

SELECT article_id
  FROM
    (SELECT article_id, count(*) AS cat
      FROM article_categories
      WHERE category_id IN (3,5,7)) arts
  WHERE arts.cat = 3;