How to query values by line with a join?

Question

Hi there,

given the two tables and values:

Tables
People- Columns: [ID]PK Name
Field - Columns: [ID FieldName]PK FieldValue

Values
People
ID Name
1  Mary
2  John
3  Tony

Field
ID FieldName  FieldValue
1  Age        20
1  Country    USA
2  Age        21
2  Country    USA
3  Age        20
3  Country    USA

I would like a query that gives me the names of the people from USA AND have 20 years old. The result should be Mary and Tony.

SELECT name FROM people p
INNER JOIN field f ON f.ID = f.ID
WHERE
(FieldName = 'Age' AND FieldValue= '20') OR
(FieldName = 'Country' AND FieldValue= 'USA'));

The problem with that query is the "OR" between the where clauses. I will get the 3 people, instead of only Mary and Tony. If I use an AND, none result is return.

Do you have some tips?

Thanks in advance :)

Answer 1

What about this?

SELECT name
FROM people p
JOIN field fAge ON p.ID = fAge.ID
   AND fAge.FieldName = 'Age'
JOIN field fCountry ON p.ID = fCountry.ID
   AND fCountry.FieldName = 'Country'
WHERE fAge.FieldValue = '20'
AND fCountry.FieldValue = 'USA'

Thereby treating the Country and Age data essentially as separate tables. As it seems that the field table is used to store different types of data.

It might be useful to have different database Views on this table for each type of data. This will further simplify the above query.

Answer 2

What you've done here, unfortunately, is implemented the Entity-Attribute-Value (EAV) antipattern. (Antipattern is a fancy word for "something that's usually a bad idea.") It's generally counter to SQL best practices to have a "Field" table like yours, which is why it has an antipattern named after it. "Entity" refers to your ID column (or, by association, a Person record), "Attribute" corresponds to FieldName, and "Value" to FieldValue.

For more information, take a look at this excellent set of slides (this should link directly to the section on EAV; if not, skip to slide 16).

The solution is to refactor your database so that Age and Country are fields in your People table. Then a far simpler query will be possible: SELECT name FROM people WHERE Age = 20 AND Country = 'USA';

Answer 3

You don't say whether you considered and rejected the "standard" design that would Name, Age, and Country in a single table. That would allow substantially easier querying and also type and range protection for the Age values as well as FK/PK protection for the Country values.

If you do have a positive reason to use the Key/Value approach, then you have three choices for your query:

1. Join the Key/Value table in the query one time for each term you're searching for (see Philip's answer, above).

2. Use WHERE EXISTS conditions:

   SELECT Name FROM People WHERE
      EXISTS (SELECT * FROM Field WHERE ID = People.ID AND FieldName = 'Country' AND Fieldvalue = 'USA')
      AND EXISTS (SELECT * FROM Field WHERE ID = People.ID AND FieldName = 'Age' AND Fieldvalue = '20')
   

3. Use some kind of INTERSECT or SUBTRACT operator if supported in your dialect of SQL.