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;
精彩评论