I hope the title makes sense, I'm fairly new to writing queries and am shaky on the terms and such.
I'd like some help understanding why my table alias isn't found on the following query. When I run it without the ON clause it executes (on SQLite Manager on Firefox) but with the clause, I get the error 'no such column t2.story_id'
NOTE - I'm using some dummy queries I created in order to simplify the problem. My final query will be structured exactly the same, so if you see any errors or things I can improve on please share. In case anyone is wondering why the two statements are pulling from the same table it's because I'll need to join two statements that pull from the same table but do very specific things that can't be done at the same time. At least I was told this was the way to do it haha.
SELECT * FROM
(
SELECT * FROM
(
SELECT story_id, role_type, user_id
FROM cur_cycle_role_activity
) t1 /* end of the first inner select statement t1 */
LEFT JOIN /* Begin Join Statement */
(
SELECT * FROM
(
SELECT story_id, workstream_num FROM cur_cycle_role_activity
) t2 /* end of the second inner select statement t2 */
) /* End Join */
ON t1.story_id = t2.story_id /* On clause for Join above */
) /* This is the end of the topmost select statement */
Error message:
[ no such column: t2.story_id ]
Wrapping your queries in SELECT * FROM (...) is causing the problem, because you can't refer to aliases in a subquery.
SELECT t1.story_id, t1.role_type, t1.user_id, t2.workstream_num
FROM (
SELECT story_id, role_type, user_id
FROM cur_cycle_role_activity) AS t1
LEFT JOIN (
SELECT story_id, workstream_num
FROM cur_cycle_role_activity) AS t2
ON t1.story_id = t2.story_id