mysql joins-如何查找属于所有父母的所有孩子 [英] mysql joins - how to find all children that belongs to ALL parents

问题描述

我有三个mysql表

items
===========
id    title

items_in_categories
============================
id    item_id    category_id

categories
===========
id    title

我想查找属于 ALL 所述类别的所有项目.不是任何一个类别，而是所有类别

I want to find all the items that belong to ALL the stated categories. Not any one category, but ALL categories

例如，如果我要搜索属于类别ID 3和5的所有项目

Eg, if I want to search all the items that belongs to category id 3 and 5

没有.可能搜索的类别最多可达20.

the no. of possible categories to be searched can go up to as many as 20.

例如，我要获取属于ID 1、2、3、4、5、6，...和20的所有商品

Eg, I want to get all the items that belongs to category id 1, 2, 3, 4, 5, 6, .... and 20

我想使用尽可能简单的方法.

I would like to use as simple a way as possible.

我已经尝试了AND，并且按照MySQL手册中的说明嵌套了NOT EXISTS.

I have tried AND and a nested NOT EXISTS as stated in the mysql manual.

什么都没做.

更新: 我想出了这个.

UPDATE: I came up with this.

select * from 
    (select count(*) as counter, item_id from items_in_categories 
        where category_id in (3, 5) group by item_id)getall 
where counter = 2

有更好的方法吗?

推荐答案

我知道这很简单，可以放在一个查询中(您可以将类别计数嵌套在had中，而不是作为一个单独的查询...我只是认为这更具可读性)，但这是我对此的看法:

I know this can be prettied up and put into one query (You could nest the category count within the having instead of as a separate query...I just think this is more readable), but here is my shot at this:

DECLARE @CategoryCount INT;

SELECT @CategoryCount = COUNT(DISTINCT id) AS CategoryCount
FROM categories
WHERE category_id IN (3,5);

SELECT *
FROM items
WHERE item_id IN 
(
    SELECT item_id
    FROM items_in_categories
    WHERE items_in_categories.category_id IN (3,5)
    GROUP BY item_id
    HAVING COUNT(DISTINCT category_id) = @CategoryCount
)

这篇关于mysql joins-如何查找属于所有父母的所有孩子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！