0

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 ]
Barmar
  • 741,623
  • 53
  • 500
  • 612
Andy
  • 281
  • 2
  • 7
  • 23
  • 2
    Why do you need all those `SELECT * FROM (subquery)`? – Barmar Nov 03 '17 at 20:57
  • 1
    Why any subquery at all? However - you should use an alias where you have `/* End Join */`. – Paul Spiegel Nov 03 '17 at 21:00
  • @Barmar I did it to make it easier for me to understand what I was doing. Sorry if it's bad practice or unnecessary. Is this causing the problem? If so I can remove them and retry! – Andy Nov 03 '17 at 21:02
  • @PaulSpiegel Ok so now I am thinking I definitely shouldn't be doing it this way. I will rewrite it removing all the subqueries – Andy Nov 03 '17 at 21:04

1 Answers1

1

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This is exactly what I needed. Thank you for the answer and explanation! It's working now, I will now rewrite my larger query with this in mind! – Andy Nov 03 '17 at 21:07