Near "(": syntax error when trying to check IS NULL. pls help!

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks

I created 2 tables to check how IS NULL clause works.

My Code:

%%sql
(SELECT track_id FROM table_1 
EXCEPT 
SELECT track_id FROM table_2
) IS NULL

What actually happened:

* sqlite:///chinook.db
(sqlite3.OperationalError) near "(": syntax error
[SQL: (SELECT track_id FROM table_1 
EXCEPT 
SELECT track_id FROM table_2
) IS NULL]
(Background on this error at: http://sqlalche.me/e/e3q8)

When I change the code to this:

%%sql
SELECT track_id FROM table_1 
EXCEPT 
SELECT track_id FROM table_2

The result is correct ( ‘track_id’ columns from both tables are the same):

* sqlite:///chinook.db
Done.

track_id

2 Likes

Can’t be entirely sure because I can’t recreate those two tables you refer to, but it is likely because the query is only applicable inside the CASE statement. Using that on a standalone query is likely not a valid syntax/code and hence the error.

2 Likes

Don`t really sure that i understand what you explained.

This is the table_1:

invoice_line_id	invoice_id	track_id	unit_price	quantity	album_title
1	1	1158	0.99	1	Use Your Illusion I
2	1	1159	0.99	1	Use Your Illusion I
3	1	1160	0.99	1	Use Your Illusion I
4	1	1161	0.99	1	Use Your Illusion I
5	1	1162	0.99	1	Use Your Illusion I
6	1	1163	0.99	1	Use Your Illusion I
7	1	1164	0.99	1	Use Your Illusion I
8	1	1165	0.99	1	Use Your Illusion I
9	1	1166	0.99	1	Use Your Illusion I
10	1	1167	0.99	1	Use Your Illusion I
11	1	1168	0.99	1	Use Your Illusion I
12	1	1169	0.99	1	Use Your Illusion I
13	1	1170	0.99	1	Use Your Illusion I
14	1	1171	0.99	1	Use Your Illusion I
15	1	1172	0.99	1	Use Your Illusion I
16	1	1173	0.99	1	Use Your Illusion I

This is the table_2:

track_id	title
1158	Use Your Illusion I
1159	Use Your Illusion I
1160	Use Your Illusion I
1161	Use Your Illusion I
1162	Use Your Illusion I
1163	Use Your Illusion I
1164	Use Your Illusion I
1165	Use Your Illusion I
1166	Use Your Illusion I
1167	Use Your Illusion I
1168	Use Your Illusion I
1169	Use Your Illusion I
1170	Use Your Illusion I
1171	Use Your Illusion I
1172	Use Your Illusion I
1173	Use Your Illusion I

May be it can help ?

1 Like

table_1:

table_2:

2 Likes

If you can share the actual code to how you created the two tables I can provide a more concrete response by testing a couple things out.

But do note, what I am trying to say is that the queries in that Mission Step work inside a CASE statement.

Once we’ve made the comparison, we can wrap it in a CASE statement to add a column that tells us if that invoice was an album purchase or not.

2 Likes
%%sql
CREATE VIEW table_1 AS
        SELECT 
                i_l.*,
                a.title AS album_title
        FROM invoice_line i_l
        INNER JOIN track t ON t.track_id = i_l.track_id
        INNER JOIN album a ON a.album_id = t.album_id
        WHERE invoice_id = 1;
%%sql
CREATE VIEW table_2 AS
SELECT t.track_id,
       a.title
FROM track t
INNER JOIN album a ON a.album_id = t.album_id
WHERE a.title = 'Use Your Illusion I';
1 Like

Ok, I will try.

But I thought the result of my origin code would be False of True.

1 Like