从过滤器中选择产品

发布于 2024-12-20 22:42:56 字数 406 浏览 0 评论 0原文

我的查询有问题：

我有 3 个表：产品（ID、名称）设置（ID、名称） Product_setting(product_id,setting_id)

例如：我想只选择您已选择过滤器的产品！

我这样做：

SELECT p. *, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)

但我得到了所有具有“设置”id = 1 或 id = 2 的产品。如何仅获取具有这些“设置”（AND）的产品？

谢谢！！

原文

I have a problem with a query:

I have 3 tables:
products (id, name)
settings (id, name)
product_setting (product_id, setting_id)

for example: I would like to select only the products you have selected filters!

I do this:

SELECT p. *, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)

but I get all products that have the 'setting' id = 1 OR id = 2.
How to get only those products that have those 'setting' (AND)?

thanks!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

转身以后 2024-12-27 22:42:56

SELECT p.*, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)
GROUP BY p.id
HAVING COUNT(*)=2; // size of IN()

SELECT p.*, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)
GROUP BY p.id
HAVING COUNT(*)=2; // size of IN()

回复收藏 0 原文

夏有森光若流苏 2024-12-27 22:42:56

这看起来有点过头了，但是……

SELECT p. *, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
INNER JOIN settings s2 ON (s.id = p2.setting_id)
WHERE 
    s.id = 1
    AND s2.id = 2

This seems like over kill but...

SELECT p. *, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
INNER JOIN settings s2 ON (s.id = p2.setting_id)
WHERE 
    s.id = 1
    AND s2.id = 2

回复收藏 0 原文

~没有更多了~