FAQ: Multiple Tables - With

Community%20FAQs%20on%20Codecademy%20Exercises

This community-built FAQ covers the “With” exercise from the lesson “Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

Learn SQL

FAQs on the exercise With

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

I’m having a hard time understanding this. I’ve forgotten what some of the commands do. Can someone go through line-by-line and explain the answer?

4 Likes

Hi,

Here is my best attempt at making the query/exercise easier to understand.

  • I put the query challenge aside for a min and consider the request in plain English.
    • The marketing department wants to know more about customers. Specifically, they want to know how many magazines each customer subscribes to AND they their name.
  • In the database there is a table called ‘orders’ and ‘customers’. We need to bring them together to get the info needed for marketing.
    • First a query is run against ‘orders’ to determine how many subscriptions each customer_id has. We get the number of subscriptions per customer id. below is the ‘orders’ query:
SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id;

Though we have the complete number subscriptions to customers, we only have the customer id’s, not names. Marketing wants names.

Using WITH we can store the results of the above query as an alias that can be used with another query to obtain the customer names. To do this we take the above (from orders) and nest it within a WITH clause and assign its result to an alias called ‘previous_query’

WITH previous_query’ AS (SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

With above we now have subscriptions per customer id stuffed into an alias called ‘previous_query’.

Next we move onto getting the customer names. To do this we perform a JOIN on ‘customers’ table. To do this we use FROM to source our alias result for customer_id and then JOIN it with ‘customers’ table to get customer names.

WITH previous_query AS (SELECT customer_id,

COUNT(subscription_id) AS 'subscriptions'

FROM orders

GROUP BY customer_id)

SELECT customers.customer_name,

previous_query.subscriptions

FROM previous_query

JOIN customers

ON previous_query.customer_id = customers.customer_id;

In order to get the specific customer names we JOINED ON alias ‘previous_query.subscriptions’ and ‘customers.customer_name’. Doing so provides a name match result for each customer id.

I know it’s a bit much, but I recommend reading the exercise and queries a few times to at least get the general idea.

Hope this helped

Robert

21 Likes

Thanks for your detailed explanation. It has helped me to understand the task better.

2 Likes

Thanks! Your explanation is much clearer.

1 Like

Is there another way to get customer names and count of subscriptions - without using WITH. @djkentuckyham

select customers.customer_name, count (orders.subscription_id) as ‘subscriptions’
from customers
join orders
on customers.customer_id = orders.customer_id
group by customers.customer_name;

1 Like

This part through me off because it one point it says to use “previous results” then the next it saying “previous query” Is that a mistake in the text?

I think previous_results is specific to the example and previous_query is specific to the instructions. It had me confused too. I think the idea that they were trying to convey is that you can use any name as an alias for WITH use_any_name_here AS ().

Thank you so much, this helped alot!

practicing different combinations of WITH iwrote this:

WITH countorder
AS (SELECT customer_id, COUNT(subscription_id) AS ‘counter’ FROM orders GROUP BY customer_id)
SELECT *
FROM customers
JOIN countorder
ON customers.customer_id = countorder.customer_id;

But in the resulting query I get two customer_id columns can someone explain why?

In the project of “Multiple tables with REBU”, I realized that WITH clause exists only during a single statement. This means I cannot claim it at top of the file then use it later on.
It would be great to mention this in the WITH section.
Please correct me if I’m wrong.

Following is the observation from the REBU project. Done in browser.

WITH mergeTable as(
 SELECT * FROM table1
 UNION
 SELECT * FROM table2
)
SELECT * FROM mergeTable; -- this will show me the mergeTable.
WITH mergeTable as(
 SELECT * FROM table1
 UNION
 SELECT * FROM table2
)

SELECT * FROM otherTable
--do something with it
;

SELECT * FROM mergeTable; -- this will NOT show me the mergeTable

Isn’t the purpose of WITH is to save lives and let the developer claim it at top of the file, then use it later on with a peace in mind?

I think these have confused me more because we switch between two data sets with similar data. Newspapers/Online and Orders/Customers are very similar, but for instance the former has first and last names separate, but the latter has the first and last name under the same column.

Why use the WITH clause in this situation? The second method is shorter, is there a downside?

Heya,

I’m a bit confused about the following section:

SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query

As far as I understand customers.customer_name is not part or previous_query and I wonder why we can select it FROM previous_query. How does that work?
Thanks :slight_smile:

Hey!

Remember that the query is better understood as a whole, context is important, so, consider that after the FROM previous_query comes the JOIN customers which is when the SELECT customers.customer_name, previous_query.subscriptions makes sense because you are ‘selecting’ the columns resulting from the two joint tables previous_query & customers.

SELECT customers.customer_name, 
previous_query.subscriptions
FROM previous_query
JOIN customers
…

Now, you may be wondering about the order in which you’re stating the SELECT clause on each column, but that will only change the order in which each column is displayed, meaning that under this context, there’s no difference between SELECT customers.customer_name, previous_query.subscriptions & SELECT previous_query.subscriptions, customers.customer_name neither there is a difference in result by doing FROM previous_query JOIN customers or FROM customer JOIN previous_query.

Note, nonetheless, the last example holds true as long as we’re not talking about a LEFT JOIN.

Hope this helps a little bit.

I cannot seem to get the NEXT button to highlight.
I think I have the answer correct below but I cannot move forward. Help?!
WITH previous_query AS (
SELECT customer_id,
COUNT(subscription_id) AS ‘subscriptions’
FROM orders
GROUP BY customer_id)
SELECT customers.customer_name,
previous_query.subscriptions
FROM previous_query
JOIN customers
ON customers.customer_id = previous_query.customer_id;