mySQL SELECT FROM表WHERE ... AND ... AND ... AND [英] mySQL SELECT FROM table WHERE ... AND ... AND ... AND

问题描述

我有一个表"articles"，其中包含列和数据:

I have a table "articles" with columns and data:

article_id     title               body
1            This is the title   This is the body text
2            Another title       Another body text

另一个具有列和数据的表类别":

Another table "category" with columns and data:

category_id    category
1              localnews
2              visible
3              first10

还有一个带有列和数据的表类别":

And a table "categories" with columns and data:

categories_id   article_id  category_id
1               1           1
2               1           2
3               1           3
4               2           1
5               2           3

我想选择WHERE类别中的行.category_id= 1 AND = 2 AND = 3

I want to SELECT the row(s) WHERE categories.category_id = 1 AND =2 AND =3

我正在使用:

SELECT articles.article_id, articles.title, articles.body, 
categories.article_id, categories.category_id 
FROM articles, categories 
WHERE articles.article_id = categories.article_id 
AND categories.article_id = 1 
AND categories.article_id = 2 
AND categories.article_id = 3 

但是它不起作用.显然，mySQL需要另一种语法. 有人可以帮忙吗? 谢谢

but it doesn't work. Obviously mySQL needs another syntax. Can someone help? Thanks

推荐答案

SELECT 
   Articles.article_id, 
   COUNT( Categories.article_id ) AS total 
FROM CategoryArticles 
LEFT JOIN Articles USING (article_id)
WHERE 
   CategoryArticles.category_id IN (1,2,3)
GROUP BY CategoryArticles.article_id
HAVING  total = 3

我为表使用了一些不同的名称，因为在您的示例中，很难注意到category和categories之间的区别.

I used a bit different names for table because in your example the distinction between category and categories is hard to notice.

这篇关于mySQL SELECT FROM表WHERE ... AND ... AND ... AND的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！