mysql - 查询多对多给我零结果

Question

我正在处理这个表

tbl公司

|idcompany |   name   |
|   1      |   apple  |
|   2      |    ms    |
|   3      |   pepsi  |

表供应商

| idbrand  |   vName   |
|    1     | comp soft |
|    2     |   food    |
|    3     |Electronics|

tbl 供应商Xcompany

| idbrand  | idcompany |
|    1     |      1    |
|    2     |      3    |
|    1     |      2    |
|    3     |      2    |

我需要知道哪些公司属于电子和计算机软件

在这种情况下是

1 - 毫秒

此查询不返回任何结果

SELECT * FROM company c
JOIN vendorXcompany vc ON c.idcompany = vc.idcompany
JOIN vendor v ON vc.idbrand = v.idbrand
WHERE vc.idbrand = 1 and vc.idbrand = 3

我做错了什么？

Answer 1

您的 WHERE 条件排除所有行，因为 vc.idbrand = 1 和 vc.idbrand = 3 不能同时为 true。您需要做的是通过计算与任一条件匹配的行来查找与这两个条件都匹配的公司:

SELECT c.idcompany, c.name
FROM company c
JOIN vendorXcompany vc ON c.idcompany = vc.idcompany
JOIN vendor v ON vc.idbrand = v.idbrand
WHERE vc.idbrand = 1 OR vc.idbrand = 3
GROUP BY c.idcompany
HAVING COUNT(*) = 2

输出:

idcompany   name
2           ms

SQLFiddle demo