CODEWITHSUNDEEP
mysqlsql
Игорь
Игорь

Reputation: 33

MySQL. How to make a selection by multiple columns

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

Upvotes: 3

Views: 54

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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.

Upvotes: 1

Deepak
Deepak

Reputation: 1

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

Upvotes: 0

Related Questions