CODEWITHSUNDEEP
mysqlentity-attribute-value
Moon
Moon

Reputation: 22565

How to query multiple rows from EAV table design?

I have a situation where I have to use EAV table design.

I have the following two tables.

Nodes

id    name    structure_id
 1    name 1       7
 2    name 2       7

Attributes

id    node_id    name       value    structure_id
1       1        firstname  test         7
2       1        lastname   test         7
3       2        firstname  test         7

I have the following query

SELECT n.*, GROUP_CONCAT( CONCAT_WS('||', a.name, a.value) ORDER BY a.name SEPARATOR ';;' ) as _attributes
            FROM nodes n JOIN attributes a ON n.structure_id = a.structure_id where n.structure_id = 7

The above query outputs the following (only ONE row)

id: 1
name: name 1
structure_id: 7
_attributes: firstname||test;;firstname||test;;firstname||test;;firstname||test;;lastname||test;;lastname||test

How do I make it to output two rows from nodes table with their rows from attributes?

Upvotes: 0

Views: 959

Answers (2)

spiritwalker
spiritwalker

Reputation: 2257

select n.id,n.name,n.structure_id,firstname,lastname from Nodes n 
join (select node_id, a.value as firstname from Attributes a where a.name='firstname' ) matches1 on matches1.node_id = n.id 
left join (select node_id, a.value as lastname  from Attributes a where a.name='lastname' )  matches2 on matches2.node_id = n.id

Upvotes: 1

Anda Iancu
Anda Iancu

Reputation: 530

You will obtain desired result set if join nodes and attributes tables by structure_id and node_id. Desired result set: "from nodes table with their rows from attributes" .

Good luck

Upvotes: 2

Related Questions