CODEWITHSUNDEEP
sqlsql-server
Dan
Dan

Reputation: 1033

Query new column in joined table

I am attempting to solve the DataLemur problem here.

When I run my query to only take those matches from the left table I'm left with a table with a set of new columns. Now I want to query one of those new columns to identify those rows that are NULL.

I don't understand how to query this new joined table as it doesn't have a name. I've looked at other examples and am confused how they work.

My query:

SELECT * 
FROM pages p
LEFT JOIN page_likes pl ON p.page_id = pl.page_id

Current output from my query:

page_id page_name user_id page_id liked_date
20001 SQL Solutions 111 20001 04/08/2022 00:00:00
20045 Brain Exercises 121 20045 03/12/2022 00:00:00
20001 SQL Solutions 156 20001 07/25/2022 00:00:00
20045 Brain Exercises 255 20045 07/19/2022 00:00:00
20001 SQL Solutions 125 20001 07/19/2022 00:00:00
31111 Postgres Crash Course 144 31111 06/21/2022 00:00:00
31111 Postgres Crash Course 125 31111 07/04/2022 00:00:00
32728 Break the thread NULL NULL NULL
20701 Tips for Data Analysts NULL NULL NULL

The way I've thought about solving this is to put the join in a CTE and alias that CTE and then call the column. But I'm not sure that is the right solution. What is the alias of the new table and how can I call the "liked_date" column and check if it's NULL?

Upvotes: 1

Views: 79

Answers (2)

Zenilogix
Zenilogix

Reputation: 1393

As stated in the other answer, all you have to do is add a WHERE clause; perhaps you are wondering why this works. You have access to all columns of both tables entering the join, regardless of whether or not they are included in the result (even though you've specified all of them via the asterisk). Note that liked_date does not need to be qualified by an alias because the column name only exists in one table (there is no ambiguity); you have to qualify page_id with an alias because both tables have a column of the same name.

Upvotes: 1

hamed danesh
hamed danesh

Reputation: 502

This is simple, here you go:

SELECT * 
FROM pages p
LEFT JOIN page_likes pl ON p.page_id = pl.page_id 
WHERE liked_date IS NULL

Upvotes: 1

Related Questions