Querying a subquery

Question

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?

Answer 1

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)

Answer 2

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)

Answer 3

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.

Answer 4

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.