MySQL. How to make a selection by multiple columns

user_15152016 0
Viewed 44

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
mysql sql
2 Answers

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.

user_22707620

Yes, you can do this. The knack you need is the concept that there are two ways of getting tables out of the table server. One way is ..

FROM TABLE A

The other way is

FROM (SELECT col as name1, col2 as name2 FROM ...) B

user_151520420
Related