CODEWITHSUNDEEP
mysqlsql
Ben
Ben

Reputation: 345

SQL (MySQL) - How to use an assigned name from nested query in inner join

SELECT name, age
FROM (SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
      ) AS C
INNER JOIN (SELECT (Max(sales), person
            FROM C) AS D ON .....

I'm trying to write a query similar to the one above however when I refer to C in the inner join section it says it doesn't exist. How can i reference it?

Upvotes: 0

Views: 829

Answers (2)

Henrique Donati
Henrique Donati

Reputation: 327

You can reference it within the select area too:

SELECT 
 name
 , age
 , (SELECT Max(sales) FROM maintable where maintable.id = c.id) MaxSales 
FROM (
  SELECT name, age / 2
  FROM maintable
  INNER JOIN ......
) AS C

Good luck!

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can't do that you can only use a subquery in a subquery.

SELECT name, age
FROM 
(
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
) AS C
INNER JOIN
(
    SELECT Max(sales), person
    FROM
    (
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
    ) AS C
) AS D ON ....

if you mysql version support CTE 

WITH C AS ( 
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
    )
SELECT name, age
FROM C JOIN 
(
    SELECT Max(sales), person
    FROM C
) AS D ON ....

Upvotes: 3

Related Questions