How to Resolve MySQL's 'View's SELECT contains a subquery in the FROM clause' Error?

Troubleshooting "View's SELECT Contains a Subquery in the FROM Clause" Error

When creating a view using a query that contains subqueries in the FROM clause, MySQL may encounter an error message stating "View's SELECT contains a subquery in the FROM clause." This error indicates that MySQL does not allow subqueries in the FROM clause of view definitions.

Error Details

Consider the following example:

create view view_credit_status as
(
  select credit_orders.client_id,
    sum(credit_orders.number_of_credits) as purchased,
    ifnull(t1.credits_used,0) as used
  from credit_orders
  left outer join (
    select * from (
      select credit_usage.client_id, sum(credits_used) as credits_used
      from credit_usage
      group by credit_usage.client_id
    ) as t0
  ) as t1 on t1.client_id = credit_orders.client_id
  where credit_orders.payment_status='Paid'
  group by credit_orders.client_id
);

This query attempts to create a view named view_credit_status that includes subqueries in the FROM clause. However, MySQL will raise the "View's SELECT contains a subquery in the FROM clause" error.

Solution

To resolve this issue, the workaround is to create separate views for the subqueries and reference those views from the primary view. Here's how it can be done:

1. Create a view named temp_view_used_credits for the subquery related to the credit usage:

create view temp_view_used_credits as
select credit_usage.client_id, sum(credits_used) as credits_used
from credit_usage
group by credit_usage.client_id;

2. Create the main view view_credit_status using the temp_view_used_credits view:

create view view_credit_status as
(
  select credit_orders.client_id,
    sum(credit_orders.number_of_credits) as purchased,
    ifnull(temp_view_used_credits.credits_used,0) as used
  from credit_orders
  left outer join temp_view_used_credits on temp_view_used_credits.client_id = credit_orders.client_id
  where credit_orders.payment_status='Paid'
  group by credit_orders.client_id
);

This approach separates the subquery into a separate view, allowing MySQL to reference it in the primary view without violating the restriction.

The above is the detailed content of How to Resolve MySQL's 'View's SELECT contains a subquery in the FROM clause' Error?. For more information, please follow other related articles on the PHP Chinese website!