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

Question

Hi.

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

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?

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

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

Answer 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.