[KLUG Members] Data transformation

[Jamie McCarthy]

tempes at ameritech.net (Andrew Thompson) writes:

> Try this:
> 
> select tbl.User_id, a.Field_Value, b.Field_Value, c.Field_Value,
> d.Field_Value from tbl
> left join (select User_id, Field_Value from tbl where Field_id=1) as a
> on tbl.User_id = a.User_id 
> left join (select User_id, Field_Value from tbl where Field_id=2) as b
> on tbl.User_id = b.User_id 
> left join (select User_id, Field_Value from tbl where Field_id=3) as c
> on tbl.User_id = c.User_id 
> left join (select User_id, Field_Value from tbl where Field_id=4) as d
> on tbl.User_id = d.User_id;

That works (with either a GROUP BY or DISTINCT tbl.User_id :)

> I'm not 100% sure this query will work as written, but I THINK
> MySQL and Postgres should handle nested queries all right.

I'm quite sure Postgres does.  MySQL does, but only in version 5.0,
which is currently in alpha, and won't be recommended for production
use until probably late next year:


mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.4-beta-standard-log |
+-------------------------+
1 row in set (0.03 sec)

mysql> select distinct foo.user_id, a.field_value, b.field_value, c.field_value,
    -> d.field_value from foo
    -> left join (select user_id, field_value from foo where field_id=1) as a
    -> on foo.user_id = a.user_id 
    -> left join (select user_id, field_value from foo where field_id=2) as b
    -> on foo.user_id = b.user_id 
    -> left join (select user_id, field_value from foo where field_id=3) as c
    -> on foo.user_id = c.user_id 
    -> left join (select user_id, field_value from foo where field_id=4) as d
    -> on foo.user_id = d.user_id;
+---------+-------------+-------------+-------------+-------------+
| user_id | field_value | field_value | field_value | field_value |
+---------+-------------+-------------+-------------+-------------+
|       1 | data11      | data12      | data13      | data14      |
|       2 | data21      | data22      | data23      | data24      |
|       3 | data31      | NULL        | NULL        | data34      |
+---------+-------------+-------------+-------------+-------------+
3 rows in set (0.01 sec)


mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.0.24_Debian-10-log |
+----------------------+
1 row in set (0.00 sec)

mysql> select distinct foo.user_id, a.field_value, b.field_value, c.field_value,
    -> d.field_value from foo
    -> left join (select user_id, field_value from foo where field_id=1) as a
    -> on foo.user_id = a.user_id 
    -> left join (select user_id, field_value from foo where field_id=2) as b
    -> on foo.user_id = b.user_id 
    -> left join (select user_id, field_value from foo where field_id=3) as c
    -> on foo.user_id = c.user_id 
    -> left join (select user_id, field_value from foo where field_id=4) as d
    -> on foo.user_id = d.user_id;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select user_id, field_value from foo where field_id=1) as a
on 
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie at mccarthy.vg