主页 / dba / 问题 / 225625
Accepted
gstlouis
Asked: 2018-12-23 10:30:18 +0800 CST

从两个表的匹配项中提取多个条目

  • 772

我有两个表，attributes 和 attributes_entries

attributes 保存标题，attribute_entries 保存该标题的值。

如果我做：

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM   attribute_entries 
INNER JOIN attributes 
       ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'ford' 
      AND attributes.title = 'manufacturer'

我可以得到以下结果：

ent_title | attr_title
ford      | manufacturer

如果我做另一个查询：

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM   attribute_entries 
INNER JOIN attributes 
      ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'whatev' 
      AND attributes.title = 'nickname'

我得到以下结果：

ent_title | attr_title
whatev    | nickname
whatev    | nickname

这两个查询都与我在那里的数据一致。

所以我认为这会起作用：

SELECT attribute_entries.title AS ent_title, 
       attributes.title AS attr_title 
FROM attribute_entries 
INNER JOIN attributes 
      ON attribute_entries.FK_attribute_id = attributes.id 
WHERE attribute_entries.title = 'ford' 
      AND attributes.title = 'manufacturer' 
      AND attribute_entries.title = 'whatev' 
      AND attributes.title = 'nickname'

这显然是行不通的。在一份声明中获得结果的最佳方法是什么？

mysql eav

1 个回答

  1. Best Answer

    您应该使用对过滤：

    WHERE (attribute_entries.title = 'ford' AND attributes.title = 'manufacturer')
      OR
      (attribute_entries.title = 'whatev' AND attributes.title = 'nickname')

    或者你可以使用 IN

    WHERE attribute_entries.title IN ('ford', 'whatev')
      AND attributes.title IN ('manufacturer', 'nickname')

    如果您需要两个条件都为真，则可以使用 EXISTS：

    WHERE EXISTS(<your first query>)
      AND
      ExISTS(<your second query>)

    你可以检查它：db<>fiddle here

    • 2

相关问题