Complicated selection in MySQL (EAV table)

Question

So I have a table like this:

|---group---|---property---|---value---|
|     a     |   prop1      |    yes    |
|     a     |   prop2      |    yes    |
|     a     |   prop3      |    no     |
|     b     |   prop1      |    yes    |
|     b     |   prop2      |    no     |
|     b     |   prop3      |    no     |
|     c     |   prop1      |    no     |
|     c     |   prop2      |    no     |
|     c     |   prop3      |    no     |

I need to filter out only those groups that have yes near prop1. I could use having statement, but this would take any yes value. The problem is that I do not have my properties as column names but as rows in a column instead. Soooo... is it even possible to do this?

There should be only groups a and b in my resulting table...

``SELECT `group` FROM `tablename` WHERE `property` = 'prop1' AND `value` = 'yes'``? — Roland Starke, Aug 12 '19 at 08:03
`where property = 'prop1' and value = 'yes'`? Otherwise do as @jarlh said. — Ocaso Protal, Aug 12 '19 at 08:04

mkRabbani · Accepted Answer · 2019-08-12T08:13:18.173

3

You can use sub query to pick first the list of groups that has value Property = 'prop1' and Value = 'Yes'. Then select all records for those list of Groups as below-

SELECT * 
FROM your_table
WHERE Group IN(
    SELECT DISTINCT `Group`
    FROM your_table
    WHERE `Property` = 'prop1'
    AND `Value` = 'Yes'
)

You can add several different properties as-

....
WHERE 
(
    `Property` = 'prop1'
    AND `Value` = 'Yes'
)
AND -- You can also use OR here if you need rows if any of the condition is true
(
    `Property` = 'prop3'
    AND `Value` = 'No'
)

edited Aug 12 '19 at 08:13

answered Aug 12 '19 at 08:05

mkRabbani

15,102
2
14
20

This works, but what if I want to combine several different properties? For example if I want `prop1` to be `yes` and `prop3` to be `no`? – milka1117 Aug 12 '19 at 08:07
you can add more condition in sub query as per requirement. – mkRabbani Aug 12 '19 at 08:08
added sample code for combining several different properties. – mkRabbani Aug 12 '19 at 08:11

score 1 · Answer 2 · answered Aug 12 '19 at 08:06

1

Single propety is straightforward

select distinct group 
from myTable
where property = 'prop1' and  value ='yes';

answered Aug 12 '19 at 08:06

Serg

20,397
5
20
44

And what about multiple properties? – milka1117 Aug 12 '19 at 08:07
See https://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema – Serg Aug 12 '19 at 08:09

score 1 · Answer 3 · answered Aug 12 '19 at 08:12

You can use EXISTS:

select t.* from tablename t
where exists (
  select 1 from tablename
  where `group` = t.`group`
  and (
    (property = 'prop1' and `value` = 'yes')
    or
    (property = 'prop2' and `value` = 'no') 
  )
)

You can combine conditions in the subquery.

score 0 · Answer 4 · answered Aug 12 '19 at 08:34

0

Just FYI, you can also write this (although it may have more limited ability to utilise indexes...

SELECT * FROM my_table WHERE(x,y) IN((1,'yes'),(2,'no'),(3,'maybe'));

answered Aug 12 '19 at 08:34

Strawberry

33,338
13
38
57

Complicated selection in MySQL (EAV table)

4 Answers4