What's causing this error?

Q&A DQ Courses
, , ,
#1

Screen Link:

Building And Organizing Complex Queries — The With Clause | Dataquest
My Code: 

with stuff as 
(SELECT
         pt.playlist_id as playlist_id,
         p.name as playlist_name,
         t.name,
         t.milliseconds *1000 as length_seconds
     FROM track t
     LEFT  JOIN playlist_track as pt ON pt.track_id= t.track_id
     LEFT JOIN playlist as p ON p.playlist_id = pt.playlist_id
 
 )
 select playlist_id, playlist_name, count(t.name) as number_of_tracks, length_seconds
 from stuff
 ORDER BY playlist_id ASC

What I expected to happen:

The correct output showing the desired columns

What actually happened:

(sqlite3.OperationalError) no such column: t.name
[SQL: with stuff as  (SELECT          pt.playlist_id as playlist_id,          p.name as playlist_name,          t.name,          t.milliseconds *1000 as length_seconds      FROM track t      LEFT  JOIN playlist_track as pt ON pt.track_id= t.track_id      LEFT JOIN playlist as p ON p.playlist_id = pt.playlist_id    )  select playlist_id, playlist_name, count(t.name) as number_of_tracks, length_seconds  from stuff  ORDER BY playlist_id ASC]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Why is it saying column t.name does not exist? I am trying to call the name column in the track table which my alias is t.

Looking at the screen here:
SQL Joins And Working With Complex Databases — Working With Larger Databases | Dataquest

You can clearly see that the column name is in the t table.

It is also pulling from the playlist table instead of track table.


WITH playlist_info AS
    (
     SELECT
         p.playlist_id,
         p.name playlist_name,
         t.name track_name,
         (t.milliseconds / 1000) length_seconds
     FROM playlist p
     LEFT JOIN playlist_track pt ON pt.playlist_id = p.playlist_id
     LEFT JOIN track t ON t.track_id = pt.track_id
    )```
Why is that?
#2

The error occurs for the following part of the query -

You are selecting from stuff, which doesn’t have a t.name column. There is no t defined for that query either. Hence the error for count(t.name).

I recommend that instead of the above, you use

SELECT * FROM stuff

to first see what the column names are for stuff and then use those accordingly in your query. Or you can add an alias for t.name when you create the subquery stuff.

#3

Why isn’t there a t.name column in stuff? I’m pulling from the track t table which has the name column in it right? So with t.name, I’m calling the name column from that table, which is not associated with stuff right?

#4

Since you didn’t provide an alias for t.name when creating stuff, the column was set to name instead since that was the column name in track. MySQL does it by default it seems (I can’t find official documentation stating so right now). Hopefully, it’s clearer now.