how do you Use the "With" Clause in MySQL

Question

I am converting all my MSSQL queries to mysql and my queries that has "with" in them are all failing

with t1 as
(
     SELECT article.*, userinfo.*, category.* FROM question
     INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid
     INNER JOIN category ON article.article_categoryid=category.catid
     WHERE article.article_isdeleted = 0
)
select t1.* from t1 ORDER BY t1.article_date DESC Limit 1, 3

Answer 1

In Sql the with statement specifies a temporary named result set, known as a common table expression (CTE). It can be used for recursive queries, but in this case, it specifies as subset. If mysql allows for subselectes i would try

select t1.* 
from  (
      SELECT article.*, 
        userinfo.*, 
        category.* 
      FROM question INNER JOIN 
        userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid
      WHERE article.article_isdeleted = 0
     ) t1
ORDER BY t1.article_date DESC Limit 1, 3

Answer 2

That feature is called a common table expression http://msdn.microsoft.com/en-us/library/ms190766.aspx

You won't be able to do the exact thing in mySQL, the easiest thing would to probably make a view that mirrors that CTE and just select from the view. You can do it with subqueries, but that will perform really poorly. If you run into any CTEs that do recursion, I don't know how you'd be able to recreate that without using stored procedures.

EDIT: As I said in my comment, that example you posted has no need for a CTE, so you must have simplified it for the question since it can be just written as

SELECT article.*, userinfo.*, category.* FROM question
     INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid
     INNER JOIN category ON article.article_categoryid=category.catid
     WHERE article.article_isdeleted = 0
 ORDER BY article_date DESC Limit 1, 3

Answer 3

MySQL doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle), so you are left with using:

• TEMPORARY tables
• DERIVED tables
• inline views (effectively what the WITH clause represents - they are interchangeable)

The request for the feature dates back to 2006.

As mentioned, you provided a poor example - there's no need to perform a subselect if you aren't altering the output of the columns in any way:

  SELECT * 
    FROM ARTICLE t
    JOIN USERINFO ui ON ui.user_userid = t.article_ownerid
    JOIN CATEGORY c ON c.catid =  t.article_categoryid
   WHERE t.published_ind = 0
ORDER BY t.article_date DESC 
   LIMIT 1, 3

Here's a better example:

SELECT t.name,
       t.num
  FROM TABLE t
  JOIN (SELECT c.id
               COUNT(*) 'num'
          FROM TABLE c
         WHERE c.column = 'a'
      GROUP BY c.id) ta ON ta.id = t.id