加入两个表，同时按索引进行分组，并为多个外部ID加工 [英] JOIN two tables while grouping by an index and maching for multiple foreign ids

问题描述

我有以下3个表格：

对象：

  ObjectID ObjectDescription 
 1first
 2second
  

属性：

  AttributeID AttributeDescription 
 1att1
 2att2
 3att3 
 4att4
  

attributelink：

  AttributeID ObjectID 
 1 1 
 2 1 
 4 1 
  

现在我的问题是：我现在想要选择一些属性并想知道哪个对象具有所有我选择的属性。我尝试了以下方法：

  SELECT * FROM`objects` 
 INNER JOIN`attributelink` 
 ON`objects`.`ObjectID` =`attributelink`.`ObjectID` 
 WHERE`attributelink`.`AttributeID` = 1 AND`attributelink`.`AttributeID` = 2 
 GROUP BY`objects`。 `ObjectID` 
  

这显然不起作用，因为一行不能有2个AttributeID，但我怎么能这样做？

>为每个选中的属性检查一次表：

  SELECT o.ObjectID 
 FROM objects o 
 INNER JOIN属性链接a1 ON o.ObjectID = a1.ObjectID AND a1.AttributeID = 1 
 INNER JOIN属性链接a2 ON o.ObjectID = a2.ObjectID AND a2.AttributeID = 2 
 GROUP BY o。 ObjectID 
  

您的测试数据并没有显示出是否有效，但FWIW， 这里是sqlfiddle 。

---

另一种方法是使用 COUNT DISTINCT 和 HAVING 和 GROUP BY （ sqlfiddle ）：

  SELECT o.ObjectID 
 FROM objects o 
 INNER JOIN attributelink a ON o.ObjectID = a.ObjectID 
 WHERE a .AttributeID IN（1,2） - 在此过滤属性上的行以测试
 GROUP BY o.ObjectID 
 HAVING COUNT（DISTINCT（a.AttributeID））= 2  - 属性数量，意思是有全部
  

I have the 3 Following Tables:

objects:

ObjectID ObjectDescription
1        "first"
2        "second"

attributes:

AttributeID AttributeDescription
1           "att1"
2           "att2"
3           "att3"
4           "att4"

attributelink:

AttributeID ObjectID
1           1
2           1
4           1

Now my question: I want now have some attributes selected and want to know, which Object has all my selected Attributes. I've tried the following:

SELECT * FROM `objects` 
    INNER JOIN `attributelink` 
         ON `objects`.`ObjectID` = `attributelink`.`ObjectID`
    WHERE `attributelink`.`AttributeID` =1 AND `attributelink`.`AttributeID` =2  
GROUP BY `objects`.`ObjectID` 

That obviously doesn't work, because one row can't have 2 AttributeIDs, but how can I archieve this?

解决方案

You have to join on the attributelink table once for each selected attribute you want to check for:

SELECT o.ObjectID
FROM objects o
INNER JOIN attributelink a1 ON o.ObjectID = a1.ObjectID AND a1.AttributeID = 1
INNER JOIN attributelink a2 ON o.ObjectID = a2.ObjectID AND a2.AttributeID = 2
GROUP BY o.ObjectID

Your test data doesn't show a whole lot about whether it works or not, but FWIW, here's the sqlfiddle.

---

Another way to do it is to use COUNT DISTINCT with HAVING and GROUP BY (sqlfiddle):

SELECT o.ObjectID
FROM objects o
INNER JOIN attributelink a ON o.ObjectID = a.ObjectID
WHERE a.AttributeID IN (1,2) --here you filter the rows on the attributes to test
GROUP BY o.ObjectID
HAVING COUNT(DISTINCT(a.AttributeID)) = 2 --# of attributes, means "having ALL"

这篇关于加入两个表，同时按索引进行分组，并为多个外部ID加工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！