Create saved query in MySQL, nesting

Microsoft Access has a nice feature which lets you create queries and also link them together. This can also be done with other platforms like MySQL however the difference is individual queries cannot be saved and later linked, it must be all done in one script. Its basically multiple “SELECT * FROM” in one query.

Examples
Microsoft Access:
SELECT *
FROM table
INNER JOIN saved_query
ON table.field = saved_query.field;

MySQL:
SELECT *
FROM table as T
INNER JOIN
(SELECT * FROM another_table) as P
ON T.field = P.field

The difference between the two examples is MySQL needs to nest any additional query and also name each (eg: as T).

It’s a lot easier for me to write a query in Access. What I did was take the Access SQL code and replace (copy and paste) the saved queries with the full code.  See the complex example below.

Access code:
SELECT year.Year, [how many dividends were paid a year].*, [Price average per year].ticker, [Price average per year].AvgOfhigh, [how many dividends were paid a year].[sumofdividend]/[Price average per year].[AvgOfhigh]*100 AS yield
FROM ([year]
LEFT JOIN [how many dividends were paid a year]
ON year.Year = [how many dividends were paid a year].year)
LEFT JOIN [Price average per year]
ON year.Year = [Price average per year].year
WHERE ((([Price average per year].ticker)=[how many dividends were paid a year].[ticker]))
ORDER BY [Price average per year].ticker;

MySQL code:
SELECT t.Year, p.*, k.ticker, k.AvgOfhigh, p.`sumofdividend`/k.`AvgOfhigh`*100 AS yield
FROM (year as t
LEFT JOIN (SELECT dividend.ticker, Count(dividend.ticker) AS CountOfticker, Left(dividend.Date,4) AS year, Sum(dividend.dividend) AS SumOfdividend
FROM dividend
GROUP BY dividend.ticker, Left(dividend.Date,4)
ORDER BY dividend.ticker ) as p
ON t.Year = p.year)
LEFT JOIN (SELECT price.ticker, Left(price.Date,4) AS year, Avg(price.high) AS AvgOfhigh
FROM price
GROUP BY price.ticker, Left(price.Date,4)) as K
ON t.Year = k.year
WHERE (((k.ticker)=p.ticker))
ORDER BY k.ticker

the most common error I encounter during the conversion was that semicolon(;) and MySQL doesn’t use brackets([])

Tags:
inner join another query,  create saved query in mysql, link queries, relationship multiple queries, connect query to one another, save query as table
nesting

Leave a Reply