如何使用外部(?)条件选择所有相关的行

I have three tables:

我有三个表:

player [id, name]
attribute [id, name]
player_attribute [id, player_id, attribute_id, value]

each player can have different attributes, some of them don't have any. Now I need to search for players with certain attributes, e.g. all players that have number 11, and their first name is John. At this moment I also know id's of these attributes, where part of my query can look like: WHERE (attribute.id, attribute.value) in ((5, '11'), (18, 'John'))

每个玩家可以有不同的属性，有些没有。现在我需要搜索具有特定属性的玩家，例如所有拥有11号的玩家，他们的名字是John。此时，我还知道这些属性的id，我的查询的一部分可以是:where(属性)。id, attribute.value, in (5， '11')， (18， 'John')

I have to get all players that meets all requested attributes.

我必须得到满足所有要求属性的所有玩家。

The problem is I don't know how the whole query have to look like? I tried joins, nested selects, grouping by but I'm not able to make it work. I'm always getting too much, or not enough rows.

问题是我不知道整个查询是怎样的?我尝试了加入，嵌套选择，分组但是我不能让它工作。我总是得到太多，或者没有足够的行。

Can you help me please?

你能帮我一下吗?

EDIT Thanks to aranid I've found a solution:

感谢aranid，我找到了一个解决方案:

select player_id from (
    select * from player_attribute where (attribute.id, attribute.value) in ((5, '11'), (18, 'John'))) 
group by player_id
having count(*) = 2

so, the keyword was having by used properly :)
Is there any way to transform above query into JOIN statement?

因此，关键字是正确使用:)是否有任何方法可以将上述查询转换为连接语句?

2 个解决方案

#1

Hope I got your question right. This should return all player_ids which have an attribute (5, '11') and another one with (18, 'John'):

希望我答对了你的问题。这将返回所有具有属性(5，'11')的player_id，以及带有属性(18，'John')的id:

Select a1.player_id
From player_attribute a1
Join player_attribute a2 On ( a2.player_id = a1.player_id )
Where a1.attribute_id = 5 And a1.value = '11'
  And a2.attribute_id = 18 And a1.value = 'John'

Any particular reason for not storing these attributes in the player table?

不将这些属性存储在player表中有什么特别的原因吗?

player (id, first_name, last_name, player_number, ...)

This would make such queries much easier

这将使此类查询更加容易

Select id
From player
Where first_name = 'John' And player_number = 11

#2

You're using the Entity-Attribute-Value design, and this is just awkward for any relational database.

您使用的是实体-属性值设计，这对于任何关系数据库来说都是很尴尬的。

You'd probably be happier using one of the new non-relational databases such as CouchDB or MongoDB. These are document-oriented databases, designed for exactly the scenario you have, where users can define their own attributes and you don't know the attributes at the time you design the database.

使用一种新的非关系数据库(如CouchDB或MongoDB)，您可能会更高兴。这些是面向文档的数据库，专门针对您所拥有的场景设计，用户可以在其中定义自己的属性，在设计数据库时不知道属性。

#1