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
– Dan, Commented May 11, 2016 at 15:05
Can you please replace every instance of ` to ' in your query so that it works? — David Söderlund
– David Söderlund, Commented May 11, 2016 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
– Tom H, Commented May 11, 2016 at 15:10
I see, I figured as much. Was confused because the question was also tagged as sql-server. — David Söderlund
– David Söderlund, Commented May 11, 2016 at 15:12

Tom H · Accepted Answer · 2016-05-11 15:36:43Z

1

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.

answered May 11, 2016 at 15:36

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FloPinguin Over a year ago

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. My next questions will have the structure included :)

Tom H Over a year ago

Ahh. With all of the other column names and tables being in English I didn't suspect another language.

Collectives™ on Stack Overflow

Use column of parent query in subquery of a subquery

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related