CODEWITHSUNDEEP
mysql
Navid_pdp11
Navid_pdp11

Reputation: 4032

How to retrieve data from a table which stores key/value as a regular table?

I have a meta table with this structure:

--------------------------------------
| id | res_id | meta_key | meta_value|
-----+--------+----------+------------
| 1  |  5     |name      | test1     |
-----+--------+----------+------------
| 2  |  5     |family    | test2     |
-----+--------+----------+------------
| 3  |  5     | title    | test      |
--------------------------------------

and resource table:

-----------------------------
| id | indate   | cost      |
-----+----------+-----------+
| 5  | 14448565 | 200000    |
-----------------------------

Now I want create a query to get this result:

-----+--------+--------+-------+------+-----------
| id | name   | family | title |cost  | indate   |
-----+--------+--------+--------------+-----------
| 5  |  test1 |test2   | test  |200000| 14448565 |
-----+--------+--------+--------------+-----------

Is there any way to do this work?

Upvotes: 0

Views: 28

Answers (2)

Blank
Blank

Reputation: 12378

Also a simple way to pivot table:

select
    r.id,
    p_meta.name,
    p_meta.family,
    p_meta.title,
    r.indate,
    r.cost
from (
    select
        res_id,
        max(case when meta_key = 'name' then meta_value end) as name,
        max(case when meta_key = 'family' then meta_value end) as family,
        max(case when meta_key = 'title' then meta_value end) as title
    from meta
    group by res_id ) p_meta
inner join resource r
on r.id = p_meta.res_id

SUB QUERY DEMO

.

Or just use LEFT JOIN:

select
    m.res_id as id,
    max(case when m.meta_key = 'name' then meta_value end) as name,
    max(case when m.meta_key = 'family' then meta_value end) as family,
    max(case when m.meta_key = 'title' then meta_value end) as title,
    r.indate,
    r.cost
from meta m
left join resource r
on m.res_id = r.id
group by m.res_id

LEFT JOIN DEMO

.

I'll prefer to sub query which can simply be understood.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You can use inner join on meta

select  b.meta_value as name,  c.meta_value as family, d.meta_value as title, 
        a.cost, a.indate
from resource as a
inner join meta as b on a.id = b.res_id and b.meta_key = 'name'
inner join meta as c on a.id = c.res_id and c.meta_key = 'family'
inner join meta as d on a.id = d.res_id and d.meta_key = 'title';

Upvotes: 1

Related Questions