查询关系表对另一列 [英] query relation table against another column

问题描述

我有两个表，实体和组件，每个表都有一个名称和唯一的ID.第三个表映射了这两个ID之间的多对多关系，称为构造"，其中包含两列，entity_ID和component_ID.实体由一定数量的组件组成.因此，如果构造表如下所示:

I have two tables, entities and components, each with a name and unique ID. There is a third table mapping the many-to-many relationship between these two IDs called construction, containing the two columns, entity_ID and component_ID. An entity is composed of some number of components. So if the construction table looks like this:

+----------+---------------+
|entity_id |  component_id |
+----------+---------------+
|        1 |             1 | 
|        1 |             2 | 
|        2 |             2 | 
|        2 |             6 | 
+----------+---------------+

实体1由组件1,2组成，而实体2由组件2和6组成.我正在处理的应用程序还有一个可用表，其中包含所有可用组件的ID.我的问题是，如何查询数据库以返回严格由可用表中的组件构成的实体?在给定的示例中，如果1,2和6都在可用表中，则应返回实体1和2.否则，如果可用表中没有2(但1和6不在)，则不返回任何内容.我是MySQL的新手，所以如果您能同时解释逻辑，那就太好了.

Entity 1 is made out of components 1,2, while entity 2 is made out of 2 and 6. The application I'm working on also has an available table containing the IDs of all available components. My question is this, how do I query the database to return the entities constructed strictly of components in the available table? In the given example, if 1,2 and 6 are all in the available table, then entities 1 and 2 should be returned. Otherwise, if 2 isn't in the available table (but 1 and 6 are) then nothing is returned. I'm new to MySQL so if you could explain the logic as well that would be great.

推荐答案

我测试了@Abdullah的子查询，确实得到了与我想要的相反的东西.经过一些测试之后，我最终得到了嵌套查询，这些查询为我提供了我想要的东西:

I tested @Abdullah's subquery and indeed got the opposite of what I wanted. After a little testing I ended up with the nested queries that give me what I was looking for:

select distinct entity_id from construction 
where entity_id not in (
    select entity_id from construction c left outer join available a on a.id = c.component_id 
    where a.id is null 
);

这篇关于查询关系表对另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！