SQL - how to select all related rows with external (?) conditions

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'))

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 开发者_StackOverflowby 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:

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?

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

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 (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

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.