How to select data from a key value pair table

Question

Table A

id,parentID, key, value
1, 2, name, name1
2, 2, age, 20
3, 2, place, place1

50, 7, name, namex
51, 7, age, 20
52, 7, place, place1
........
101, 5, name, namez
102, 5, age, 23
103, 5, place, place2

I need to get all the date having plave = place1 and age = 20 in the bellow format

parentid, name, age, place
2, name1, 20, place1
7, namex, 20, place1

How to write the Mysql SQL query please help me

Just for future use, you can find a lot more helpful info on this topic if you search using the terms Entity Attribute Value (EAV) model vs. a Key Value table. — user1544428, Feb 14 '21 at 01:17
@user1544428 thank you a lot, didn't know the term for the concept! Now everything is in place. — Kirill Starostin, Jan 11 '22 at 09:20

score 10 · Answer 1 · answered Jul 14 '17 at 21:48

You can use conditional aggregation to get all the info for a parentid on to one row and then use a where clause for the required condition.

select * from (
select parentid
,max(case when key='name' then value end) as name
,max(case when key='age' then value end) as age
,max(case when key='place' then value end) as place
from tableA
group by parentid 
) t
where place='place1' and age=20

This assumes there is only one row per key per parentid in the table.

score 1 · Answer 2 · answered Jul 14 '17 at 21:48

You need to join the table three times - once for the name, once for the age and last for the place. This will de-normalize your table, and then it's simple query to filter the criteria you want:

SELECT a.parentId, a.name, b.age, c.place from 
myTable as a inner join myTable as b on a.parentId = b.parentId
inner join myTable as c on a.parentId = c.parentId
where a.id = 1 and b.id = 2 and c.id = 3 
and b.age = 20 and c.place = 'place1';

score 0 · Answer 3 · answered Jul 14 '17 at 21:47

In the SELECT clause, you especify the column names you want in the result, in the FROM clause, you especify the tables where you are going to apply the query to, and in the WHERE clause you especify the conditions each row must meet in order to be on the result:

SELECT parentid, name, age, place 
FROM tableA
WHERE place="place1" AND age=20

You can check this link for more information.

score 0 · Answer 4 · answered Jul 14 '17 at 22:19

You need to use a lot of sub-query like this

SELECT 
    parentId,
    (SELECT aName.value FROM TableA aName WHERE aName.parentId = a1.parnetId and aName.KEY = 'name') as name,
    value as age,
    (SELECT aPlace.value FROM TableA aPlace WHERE aPlace.parentId = a1.parnetId and aPlace.KEY = 'place') as place
FROM TableA a1
WHERE 
    key = 'age' 
    AND 
    value = '20'
    AND 
        EXISTS (SELECT NULL FROM TableA a2 
            WHERE 
                a1.parentId = a2.parentId 
                AND
                a2.key = 'place'
                AND
                a2.value = 'place1')

How to select data from a key value pair table

4 Answers4

Linked