Use column of parent query in subquery of a subquery

Question

With this query:

SELECT
    `id`,
    `type`,
    `subtype`,
    `title`,
    `shortdesc`,
    (SELECT COUNT(*)
     FROM `story_comments`
     WHERE `parent_id` = t1.`id`) as comments,
    (SELECT
        (ROUND( (
            SELECT (SUM(`rating` * `count`) / SUM(`count`) ) ) * 2) ) / 2 as result
            FROM
                    (SELECT rating, COUNT(*) as count
                     FROM `story_ratings` WHERE `parent_id` = t1.`id`
                     GROUP BY rating) as val) as rating,
    `calls`,
    `user`
FROM
    `storys` t1
WHERE
    `open` = 1 AND
    `modremove` = 0 AND
    `modblock` = ''
ORDER BY
    `opening`
DESC LIMIT 16;

I get this error: #1054 - Unknown column 't1.id' in 'where clause', which is caused by the subquery in the subquery (subquery after FROM).

But the t1.id in the first subquery is working fine. Why cant I use it in the FROM-subquery? I also tried variables, which also didnt work:

SELECT @i := `id` id, `type`, `subtype`, `title`, `shortdesc`, (SELECT COUNT(*) FROM `story_comments` WHERE `parent_id` = t1.`id`) as comments, 

(SELECT (ROUND( (SELECT (SUM(`rating` * `count`) / SUM(`count`) ) ) * 2) ) / 2 as result FROM (SELECT rating, COUNT(*) as count FROM `story_ratings` WHERE `parent_id` = @i GROUP BY rating) as val) as rating,

`calls`, `user` FROM `storys` t1 WHERE `open` = 1 AND `modremove` = 0 AND `modblock` = '' ORDER BY `opening` DESC LIMIT 16;

With the @i variable, result returned NULL on every row, what is wrong.

Maybe look into Common Table Expressions (CTE), it might help clean up your SQL. — Dan, May 11 '16 at 15:05
Can you please replace every instance of ` to ' in your query so that it works? — David Söderlund, May 11 '16 at 15:08
@DavidSöderlund The backtick is a quoted identifier for MySQL. Those are supposed to be names, not strings and in MySQL it should work fine, even if it is ugly (IMO) — Tom H, May 11 '16 at 15:10
I see, I figured as much. Was confused because the question was also tagged as sql-server. — David Söderlund, May 11 '16 at 15:12
Does mySQL support window functions? This code could be made pretty by cross applying this as rating -> SELECT ROUND(SUM(sr.rating) over (partition by t1.id) / count(*) OVER (partition by t1.id) * 2) / 2 AS totalRating FROM story_ratings as sr WHERE sr.[parent_id] = t1.[id] — David Söderlund, May 11 '16 at 15:20

score 1 · Accepted Answer · answered May 11 '16 at 15:36

Wow. So many nested subqueries. Instead of nesting queries to the ends of the earth, use JOINs and aggregate your data to calculate what you need. I had to make some guesses about your table structures because you didn't supply them (something that you should always do when posting a database question).

SELECT
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    COUNT(DISTINCT SC.id) AS comments,
    AVG(SR.rating) AS rating,
    calls,
    user
FROM
    Storys S  -- Storys isn't the plural of Story, BTW
LEFT OUTER JOIN Story_Comments SC ON SC.parent_id = S.id
LEFT OUTER JOIN Story_Ratings SR ON SR.parent_id = S.id
WHERE
    S.open = 1 AND
    S.modremove = 0 AND
    S.modblock = ''
GROUP BY
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    S.calls,
    S.user
ORDER BY
    opening
DESC LIMIT 16;

I don't think that the "* 2 ... / 2" does what you think it does, based on the various parentheses and the rounding might not be right here depending on the data type of your rating column (again, no table structures, so I'm stuck guessing).

I don't have a MySQL server handy, but testing this code on SQL Server (adjusting for difference in the ROUND function) gave the same results as your first query.

Thank you very much! With some changes, it worked. I really have to learn JOINs... In German, we also say "Storys" as the plural of "Story". The thing with *2 and /2 achieves something like this: [link](http://stackoverflow.com/questions/14903379/rounding-to-nearest-fraction-half-quarter-etc). My next questions will have the structure included :) — FloPinguin, May 11 '16 at 16:02
Ahh. With all of the other column names and tables being in English I didn't suspect another language. — Tom H, May 11 '16 at 17:22

Use column of parent query in subquery of a subquery

1 Answers1

Linked