0

I am currently working the first time with mysql and Views I came into it quite well but when I execute this SQL-statement I ran into an error. I sadly couldn't find a solution yet. It says that the view contains a subquery but I can't see or realise any subquery in this statement.

I am refering to this post: Adding extra column to view, which is not present in table

The guy there is using similar code and for him it is working, no idea why it isn't in my example. I also looked through Stackoverflow and saw a load of the same errors but the subquery was quite obvious in the other topics.

CREATE OR REPLACE VIEW 
`Worker_!view` AS 
SELECT * FROM 
(
   SELECT `Working_!skill`, 
   'asdf_!electrical' charserver 
   FROM `asdf_!electrical` 
   WHERE 1 ORDER BY `id` DESC LIMIT 1 
   UNION 
   SELECT `Working_!skill`, 'fred_!electrical' charserver 
   FROM `fred_!electrical` WHERE 1 ORDER BY `id` DESC LIMIT 1
);

#1349 - View's SELECT contains a subquery in the FROM clause

Community
  • 1
  • 1
JustRandom
  • 487
  • 1
  • 9
  • 19
  • Possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) – Christophe Weis Feb 02 '17 at 07:48

1 Answers1

2

That doesn't work in MySQL. Instead, you can do:

CREATE OR REPLACE VIEW `Worker_!view` AS 
   (SELECT `Working_!skill`, 'asdf_!electrical' charserver 
    from `asdf_!electrical` 
    WHERE 1 ORDER BY `id` DESC
    LIMIT 1 
   )
   UNION ALL
   (SELECT `Working_!skill`, 'fred_!electrical' charserver 
    FROM `fred_!electrical`
    WHERE 1
    ORDER BY `id` DESC
    LIMIT 1
  );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786