MySQL. How to make a selection by multiple columns

Question

I have a database with the following base structure.

create table objects
(
    id int auto_increment primary key,
);

create table object_attribute_values
(
    id int auto_increment primary key,
    object_id int not null,
    attribute_id int not null,
    value varchar(255) null
);

create table attributes
(
    id int auto_increment primary key,
    attribute varchar(20) null,
);

And so let's say the attribute table has 3 :

id	attribute
1	color
2	rating
3	size

I need select all objects that have color='black', rating IN (5, 10), size=10.

I understand how to get all objects in black

SELECT o.id
FROM objects o
INNER JOIN object_attribute_values oav ON oav.object_id = o.id
INNER JOIN join attributes a ON a.id = oav.attribute_id
WHERE a.attribute = 'color' AND oav.value = 'black'

The result should be like this:

object_id	attributes
1	color:black,rating:6,size:10
7	color:black,rating:6,size:10
12	color:black,rating:9,size:10

Answer 1

What you are dealing with is a key/value table. I don't like them much, because they make querying data more complex and don't guarantee consisteny (data type, obligatory/optional values) as normal columns do. But sometimes they are necessary.

Anyway, the typical way to query key/value tables is by aggregation:

SELECT
  o.id as object_id,
  GROUP_CONCAT(CONCAT(a.attribute, ':', oav.value) ORDER BY a.id SEPARATOR ';') AS attributes
FROM objects o
INNER JOIN object_attribute_values oav ON oav.object_id = o.id
INNER JOIN join attributes a ON a.id = oav.attribute_id
GROUP BY o.id
HAVING SUM(a.attribute = 'color' AND oav.value = 'black') > 0;

The HAVING clause looks for all objetcs that have color = black. Others are dismissed. This works, because in MySQL true = 1, false = 0, so we can just add up the condition results.