Querying a subquery

I'm trying to do something like this:

SELECT t1.*,
       t2.*
FROM   (SELECT *
        FROM   USER) AS t1,
       (SELECT *
        FROM   t1) AS t2

It doesn't work in MySQL. Is something like the above possible? My other option is to take the last 开发者_开发问答subsquery and "wrap" it around the main query. Ideas?

Use:

SELECT t1.*,
       t2.*
  FROM (SELECT *
          FROM USER) AS t1,
       (SELECT *
          FROM (SELECT *
                  FROM USER) AS t1) AS t2

The WITH syntax allows you built on top of CTEs:

WITH t1 AS (
  SELECT * FROM USER), -- comma separates them
     t2 AS (
  SELECT * FROM t1)
SELECT t1.*, t2.*
  FROM t1, t2

..but MySQL doesn't support the WITH clause.

Why not

SELECT t1.*, t2.* FROM USER as t1, USER as t2

? You should also add a condition or this will generate a huge amount of rows.

Now, if you meant to ask how to refer to the previous subquery, you should repeat it and add the extra conditions required:

SELECT t1.*, t2.* FROM (SELECT bar FROM foo WHERE baz=1) as t1, 
(SELECT bluz,bar FROM foo WHERE baz=1 AND quux = 0) as t2 WHERE t2.bar = t1.bar

Another option is to create a view or temporary table with the subquery (t1)

If the 2 tables have the same number of columns you could just use a UNION e.g.

SELECT *
FROM   USER
UNION
SELECT *
FROM   t1

if they don't have the same columns you will need to fins some way to join the tables, like with a foreign key. E.g.

SELECT *
FROM   USER as t1
JOIN   t1 as t2 (on t1.id = t2.id)

I assume your actual query is much more complex. Is it possible you could create a view to represent your subquery, then you could use a much simpler select statement like Vinko Vrsalovic suggested.