Error in creating view

Question

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

mycode is:

CREATE VIEW `MyViewName` AS
SELECT *
FROM
  (SELECT *
   FROM (
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)) t
   ORDER BY rand())

what is the problem with this code?I am not familier with view

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) — hjpotter92, Jul 30 '14 at 12:09

score 2 · Accepted Answer · answered Jul 30 '14 at 12:08

MySQL does not allow subqueries in the from clause when you define views.

You can do what you want as:

Create View `MyViewName` as
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union 
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    order by rand();

My guess is that you actually want to change the ctype values as well. Otherwise the query is rather strange.

Here are the restrictions on views.

actually ctype different , that's my mistake – bon Jul 30 '14 at 12:31 — bon, Jul 30 '14 at 12:31

Error in creating view

1 Answers1