Create a matrix/table from a LEFT JOIN in SQL

Question

I'd like to know a good solution for converting a LEFT JOIN into a table/matrix.

For example, given this schema:

objects
   id (integer)
   name (string)

attributes
   id (integer)
   object_id (integer)
   name (string)
   value (string)

And these values:

1,ball
2,box

1,1,colour,red
2,1,shape,sphere
3,1,material,rubber
4,2,colour,brown
5,2,shape,cube

I'd like to get this:

object_id | object_name | colour | shape  | material

1         | ball        | red    | sphere | rubber
2         | box         | brown  | cube   | NULL

Ideally this would be with an unknown number of attributes for each object, and be for MySQL without using stored procedures.

Answer 1

You cannot do it without using dynamical queries in SQL.

Any set SQL deals with assumes a fixed number of columns, with fixed names.

Otherwise it would lead to many things SQL is just not designed to deal with.

Like, imagine a view created on the query you asking for (were it possible):

SELECT  object_id, colour
FROM    myquery

Whether this view compiles or not would depend on the results of the query and this would give you runtime errors (which are hard to handle) instead of mere empty resultset (which is easy to handle).

You are basically talking about the results presentation here, and these things are usially done on the client side.

If you have attribute_names table which contains all possible names of your attributes, you can do something like this:

SELECT  o.name, an.name, a.value
FROM    objects o
CROSS JOIN
        attribute_names an
LEFT JOIN
        attributes a
ON      a.object = o.id
        AND a.name = an.name

, which will contain all possible object_id / attribute_name pairs with corresponding values, and you can use it on client side to fill a matrix more easily.

Answer 2

The thing with RDBMSs is that their purpose is to store and present what you already have. Creating columns dynamically was never intended.

This is a case that should be handled on the client that is to present the data.

With disregard to how RDBMSs are supposed to be used, you still won't find an easy way to do this. Especially not if you want the column list to be dynamic. You can build a dynamic SQL query and execute that, but there's no way to write a standard SQL query and get that result (since you always specify all columns explicitly, bar using *, but that can't be used to your advantage in this case.)

Answer 3

This would solve your problem assuming the result table is called merged, although the schema you have I'd say was better.

insert into merged (object_id, object_name) select id, name from objects;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'colour' set m.colour = a.value;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'material' set m.material = a.value;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'shape' set m.shape = a.value;