MySQL: View With Subquery in the from Clause Limitation

MySQL: View with Subquery in the FROM Clause Limitation

Couldn't your query just be written as:

SELECT u1.name as UserName from Message m1, User u1 
  WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3

That should also help with the known speed issues with subqueries in MySQL

MySQL - create view using subquery in FROM clause

Try to do it without subquery -

CREATE OR REPLACE VIEW V_TASK_TRANSFER AS
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_DOM TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE
    UNION
    SELECT T.REF_ID,
      T.DATE_CREATE,
      T.DATE_TRX,
      TTF.ACCOUNT_NO,
      TTF.TO_ACCOUNT_NO,
      TTF.TO_NAME,
      TTF.CURRENCY_CODE,
      TTF.AMOUNT,
      T.TASK_TYPE,
      TT.NAME_E,
      TT.NAME_I,
      T.REF_NO,
      T.EXECUTION_TYPE,
      T.REVIEW_COUNT,
      T.REVIEW_NEED,
      T.APPROVE_COUNT,
      T.APPROVE_NEED,
      T.TRX_COUNT_SUCCESS,
      T.TRX_COUNT_FAIL,
      T.TRX_COUNT_ALL,
      T.STATUS_TF,
      T.USER_ID
    FROM IB_TASKS T
    JOIN IB_TASK_TRANSFERS_FGN TTF ON T.REF_ID = TTF.REF_ID
    JOIN IB_TASK_TYPES TT ON TT.TASK_TYPE = T.TASK_TYPE;

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

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
  );

Creating view but get error Views SELECT contains subquery in the FROM clause

MySQL doesn't allow subqueries in the FROM clause of a view. Arggh!

You can replace this with a correlated subquery:

This is the code i am using;

CREATE VIEW MostBuys AS
    SELECT o.Kundnummer,
           (SELECT COUNT(*) AS AntalBest
            FROM OrderContent oc
            WHERE oc.OrderNr = o.OrderNr
           ) as AntalBest
    FROM Ordrar o

View's SELECT contains a subquery in the FROM clause

As per documentation:

MySQL Docs

• The SELECT statement cannot contain a subquery in the FROM clause.

Your workaround would be to create a view for each of your subqueries.

Then access those views from within your view view_credit_status

How to create view with subquery in from cluse - Maria DB

I think you can express this using union between two subqueries:

SELECT d.NIK, d.Enroll
FROM dvc0005 d
WHERE YEAR(DATE(d.Enroll)) = YEAR(CURRENT_DATE) AND
      EXISTS (SELECT 1
              FROM emp0003 e
              WHERE e.NIK = d.NIK AND e.IsActive = 'T' 
             )
UNION
SELECT d.NIK, d.Enroll
FROM dvc0004 d
WHERE YEAR(DATE(d.Enroll)) = YEAR(CURRENT_DATE) AND
      EXISTS (SELECT 1
              FROM emp0003 e
              WHERE e.NIK = d.NIK AND e.IsActive = 'T' 
             );

View's SELECT contains a subquery in the FROM clause MySQL statement

As the official documentation says

E.4. Restrictions on Views

(..)

Subqueries cannot be used in the FROM clause of a view.

One option could be creating a View for each subquery.
Another one, is modify your view, to avoid subquerys in the from clause

---

Related Topics