CODEWITHSUNDEEP
mysql
laketuna
laketuna

Reputation: 4080

Select row based on values in associated rows in EAV

I have two tables (EAV relationship), Customer (id,name) and CustomerVarchar (id,customer_id,attribute_id,value) with relationships below.

Customer hasMany CustomerVarchar

CustomerVarchar belongsTo Customer

I'd like to select a row in Customer based on two rows with specific values in CustomreVarchar. More specifically, I want to retrieve a row in Customer that has these two associated rows with the below condition (must have both of the associated rows in CustomerVarchar):

row 1 has `attribute_id`= 5 and `value`=`John`

row 2 has `attribute_id`= 7 and `value`=`Doe`

Is this possible?

Upvotes: 0

Views: 147

Answers (1)

Taryn
Taryn

Reputation: 247720

You can use the following to return the customer_id from the customerVarchar table with all of the attributes and values:

select customer_id
from customerVarchar
group by customer_id
having 
  sum(case when attribute_id = 5 and value = 'John' then 1 else 0 end) > 0
  and sum(case when attribute_id = 7 and value = 'Doe' then 1 else 0 end) > 0;

Then you can JOIN this to your customer table to return the customers with that data:

select c.id, c.name
from customer c
inner join
(
  select customer_id
  from customerVarchar
  group by customer_id
  having 
    sum(case when attribute_id = 5 and value = 'John' then 1 else 0 end) > 0
    and sum(case when attribute_id = 7 and value = 'Doe' then 1 else 0 end) > 0
) v
  on c.id = v.customer_id;

See SQL Fiddle with Demo

Upvotes: 2

Related Questions