CODEWITHSUNDEEP
mysqlsql
Emil L
Emil L

Reputation: 21081

Can't create view with query containing subquery, what can I do instead?

I want to create a view with for a table in my database so I can use it in joins more easily. The table UserSettings look like this:

mysql> desc UserSettings;  -- cut the output for readability 
+-----------+---------------------
| Field     | Type                
+-----------+---------------------
| userID    | bigint(20) unsigned 
| timestamp | timestamp           
| settingID | text                
| setting   | text                
+-----------+---------------------
4 rows in set (0.00 sec)

In it we store every update of a users settings. Now I want to create a view for this table with the latest settings for each user. So I want the view to contain the rows marked with *:

mysql> select * from UserSettings;
+--------+---------------------+-----------------------+---------+
| userID | timestamp           | settingID             | setting |
+--------+---------------------+-----------------------+---------+
|    123 | 2014-04-16 12:08:30 | settings.warnings_off | 1       |
|    123 | 2014-04-16 12:18:56 | settings.warnings_off | 0       |
|    123 | 2014-04-17 06:42:08 | settings.warnings_off | 1       |
|    123 | 2014-04-17 06:49:08 | settings.warnings_off | 0       | *
|    911 | 2014-04-17 06:49:33 | settings.warnings_off | 1       | *
|    123 | 2014-04-17 10:04:12 | settings.test         | fooo    | 
|    123 | 2014-04-17 10:04:22 | settings.test         | bar     | *
|    911 | 2014-04-17 10:07:40 | settings.test         | bar     | *
+--------+---------------------+-----------------------+---------+
8 rows in set (0.00 sec)

I can do this with a query containing a subquery:

mysql> SELECT us.* 
    -> FROM  UserSettings us INNER JOIN (
    ->     SELECT userID, settingID, max(timestamp) as timestamp 
    ->     from UserSettings 
    ->     group by userID, settingID) ts ON ts.userID = us.userID AND 
    ->   ts.settingID = us.settingID AND ts.timestamp = us.timestamp;
+--------+---------------------+-----------------------+---------+
| userID | timestamp           | settingID             | setting |
+--------+---------------------+-----------------------+---------+
|    123 | 2014-04-17 10:04:22 | settings.test         | bar     |
|    123 | 2014-04-17 06:49:08 | settings.warnings_off | 0       |
|    911 | 2014-04-17 10:07:40 | settings.test         | bar     |
|    911 | 2014-04-17 06:49:33 | settings.warnings_off | 1       |
+--------+---------------------+-----------------------+---------+
4 rows in set (0.00 sec)

However I get the error ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause when using the above query in CREATE VIEW CurrentUserSettings AS <THE QUERY ABOVE>.

Is there a way to create a view of the UserSettings table that show the same data as the query above? Perhaps using HAVING in some way?

Upvotes: 1

Views: 83

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

Like Kickstart advises, do like this

First, create the subquery in a view

CREATE VIEW subqueryview as 
SELECT userID, settingID, max(timestamp) as timestamp 
from UserSettings group by userID, settingID;

Then you can create the view using the view.

CREATE VIEW finalview AS 
SELECT us.* 
FROM UserSettings us 
-- Here you now use the view created.
INNER JOIN  subqueryview ts 
ON ts.userID = us.userID 
AND ts.settingID = us.settingID 
AND ts.timestamp = us.timestamp;

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

To expand my comment above:-

CREATE VIEW latest_timestamp AS SELECT userID, settingID, max(timestamp) as timestamp 
    from UserSettings 
    group by userID, settingID;

CREATE VIEW latest_timestamp AS SELECT us.* 
FROM  UserSettings us 
INNER JOIN latest_timestamp ts 
ON ts.userID = us.userID AND 
ts.settingID = us.settingID 
AND ts.timestamp = us.timestamp;

Another option is to (ab)use the GROUP_CONCAT function. I am not that keen on doing this and there are issues if the fields can contain NULL, plus is entails converting integers to character fields.

Assuming the fields you want are called field1, field2 and field3 then:-

SELECT us.userID,
        us.settingID,
        SUBSTRING_INDEX(GROUP_CONCAT(us.field1 ORDER BY timestamp DESC SEPARATOR '|~|~'), '|~|~', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(us.field2 ORDER BY timestamp DESC SEPARATOR '|~|~'), '|~|~', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(us.field3 ORDER BY timestamp DESC SEPARATOR '|~|~'), '|~|~', 1)
FROM  UserSettings us 
GROUP BY us.userID, us.settingID;

This works by concatenating all the values for a field up into one, ordered by the timestamp descending and with a separator between them (something that will not be in any of the fields), then using SUBSTRING_INDEX to get the field up to the first delimiter.

Upvotes: 2

Related Questions