Complicated SELECT query

Question

I have a table which defines what things another table can have, for example:

CREATE TABLE `objects` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
);

INSERT INTO `objects` (`name`) VALUES ('Test');
INSERT INTO `objects` (`name`) VALUES ('Test 2');

CREATE TABLE `properties` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
);

INSERT INTO `properties` (`name`) VALUES ('colour');
INSERT INTO `properties` (`name`) VALUES ('size');

CREATE TABLE `objects_properties` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `object_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `value` VARCHAR(50) NOT NULL,
  FOREIGN KEY (`object_id`)
    REFERENCES `objects` (`id`),
  FOREIGN KEY (`property_id`)
    REFERENCES `properties` (`id`)
);

INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 1, 1, 'red');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 1, 2, 'small');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 2, 1, 'blue');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 2, 2, 'large');

Hopefully this makes sense. Basically instead of having columns for colour, size etc. in the objects table, I have two other tables, one that defines the properties any object can have, and another that links objects to some or all of these properties.

My question is if there's some way to retrieve this information like this:

+--------+------------+------------+
| object | colour     | size       |
+--------+------------+------------+
| Test   | red        | small      |
| Test 2 | blue       | large      |
+--------+------------+------------+

So you can see the column headings are actually row values. I'm not sure if it's possible or how costly it would be compared to doing a few separate queries and putting everything together in PHP.

Answer 1

The keyword here is "pivot table" "crosstab" (but a "pivot table" lies also in that direction) and no, MySQL cannot do this directly. You can create a query that will select this, but you will have to explicitly define the columns yourself in the query. No fetching of columns from another table. Other RDBMS may have capabilities for this.

Answer 2

pivot (or something like that) could be useful. In MS SQL Server you can use it BUT the values to pivot the table must be constant or you can use a stored procedure to calculate it.

Here you can find more info.

Have a nice day!

Answer 3

SELECT o.name, c.colour, s.size
FROM objects o
LEFT JOIN (SELECT op.object_id, op.value colour
        FROM objects_properties op
        join properties p on op.property_id = p.id and p.name = 'colour') c
ON o.id = c.object_id
LEFT JOIN (SELECT op.object_id, op.value size
        FROM objects_properties op
        join properties p on op.property_id = p.id and p.name = 'size') s
ON o.id = s.object_id

Answer 4

SELECT  o.*,
        (
        SELECT  *
        FROM    object_properties op
        WHERE   op.object_id = o.object_id
                AND op.property_id = $prop_color_id
        ) AS color,
        (
        SELECT  *
        FROM    object_properties op
        WHERE   op.object_id = o.object_id
                AND op.property_id = $prop_size_id
        ) AS size
FROM    objects o

Substitute the $prop_color_id and $prop_size_id with the color and size property id's.

For this query to be efficient, make (object_id, property_id) a PRIMARY KEY in the object_properties and get rid of the surrogate key.