FAQ: Multiple Tables - Cross Join

This community-built FAQ covers the “Cross Join” 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 Cross Join

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!

Hi everyone!

So, about the CROSS JOIN almost everything is clear, only I don’t get totally where will I need to use it. But it’s like a sub-question, give answer on it with the main question if you can.

The main question is about instruction #3. Here we need to create a cross join query with a where statemant with two conditions:

start_month <= month AND end_month >= month;

(please look at the excersice to get wider understanding of what I’m talking about)

At the end of the instruction it says: “This will select all months where a user was subscribed.” The query I wrote, by this instruction, works perfectly, just I can’t undesrtand the logic behind it. Please, kindly explain how does it select’s all months where a user was subscribed?

18 Likes

Hi Norlandius,

I wanted to answer your main question. I’m guessing your SQL query looks something like this:

SELECT month,
COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month
GROUP BY month;

Line 1 is basically saying pull the column called month
Line 2 is saying, reference the [cross joined newspaper table +months table] to pull the month and count the number of rows for each month value (this value ranges from 1-12)

Line 3 adds a condition, so that the query can work for every value that’s listed in the month column from Line 1 (where the values range from 1-12) imagine this simpler query listed below is being run 12 times in the background once for each month. That’s why the above query is important and powerful.

SELECT COUNT(*)
FROM newspaper
WHERE start_month= 1
AND end_month = 1;

This exercise is useful because you can now analyze your subscription trend…is it growing, declining, at what rate etc.

Hope that helps!

3 Likes

Just adding to muqaddasibrahim’s answer.

What CROSS JOIN does is the following:
–> For each line of newspaper it will add all the lines from months.
–> This way, line 1 from table newspaper will appear 12 times now, 1 per each month. The same applies to all the other lines

–> the logic of the WHERE condition will filter out lines whose months are outside the range
start_month< month < end_month

This way you can count how many lines there are. If grouped by months, it will show how many lines there are for that specific month, which is the number of subscriptions.

7 Likes

Hi there!
I didn’t quite understand, why we use start_month <= 3
end_month >= 3 conditions instead of just ‘start month = 3’ in instruction #1. Can someone explain it to me please? Appreciate it

2 Likes

This gets explained in the Hint…

“During March” means that the customer’s starting month was in or before March and final month was in or after March:

3 Likes

Using start_month = 3 will only include subscriptions starting on March but not the ones that started before and end on March or later.

To be subscribed during March the subscription must begin in March (or before) and end on March (or after). So, the condition requires start_month <= 3 AND end_month >= 3 (where 3 is March). The following subscriptions includes March.

  • From 1 to 3 start_month = 1 AND end_month = 3
  • From 1 to 5 start_month = 1 AND end_month = 5
  • From 3 to 5 start_month = 3 AND end_month = 3

The following subscriptions doesn’t include March

  • From 1 to 2 (because end_month >= 3 is false)
  • From 4 to 5 (because start_month <= 3 is false)

So, both conditions established in the hint must be true at the same time and that requires the use of the AND clause.

4 Likes

I also struggled with this. I understand how the WHERE works and how the cross join brings up all the possibilities and then you filter out those that did not fit.
It is a very elegant method, but it is the first one where I have wondered how I would ever find another context for it and remember how to do it. Maybe that will come with later projects. I trust the trainers and I am really enjoying the course.

3 Likes

Can someone help me? I’m either really missing something or the site isn’t working for me. I copied the exact cross join they had in Lesson 3 (see image). And yet the code isn’t working for me. Can someone help me out or tell me if I’m missing the obvious?

3 Likes
SELECT month,
   COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= month 
   AND end_month >= month
GROUP BY month;

I get the code on lines 5 onward. But why do I select month, but then COUNT(*)? Why not COUNT(month)? Why is it FROM newspaper? newspaper doesn’t have a month column, so surely it would be FROM month? And so, I CROSS JOIN newspaper?

2 Likes

same issue here. I don’t know why the website keep asking me “did you aggregate by month” even though I copy exactly the same code from Hint. This pull me back for weeks andI can’t move on to next step, burning my money and time.

By reading the Cross-Join exercise and the examples , I was wondering , why in example 3 & 4, we do not simply use INNER JOIN instead of CROSS JOIN? In the Inner Join exercise it was written “When we perform a simple/inner join , the result only includes rows that match on our condition”.
By following that definition, It seems to me that INNER JOIN can be applied at the above examples since we restrict the query by writing start_month <= month & end_month >= month. which helps us make a condition match.
I actually used the INNER JOIN for example No. 3 and I wrote the code as it follows :

SELECT *
FROM newspaper
INNER JOIN months
ON start_month <= month
AND end_month >= month;

& for example No.4:

SELECT month,
COUNT(*)
FROM newspaper
INNER JOIN months
ON start_month <= month
AND end_month >= month
GROUP BY month;

There was not any error message about the code. The solutions were correct and exactly the same as those of codeacademy’s instructions which used the CROSS JOIN!

I would appreciate if someone helped me with my confusion with INNER & CROSS JOIN concepts. Thanks.

2 Likes

Looks to me that you’re missing a query. There should be four separate queries to complete this challenge.

1 Like

It seems like CodeAcademy’s check is too rigorous. I had set limit’s for a couple of these queries because I wanted to save scrolling time, when I check my queries, but as a result it would not let me finish the lesson until I removed them.

Dumb.

1 Like

So I get the idea of SQL CROSS JOIN, but there’s something I noticed.

All the examples we’ve treated so far have a single column in the second table.

How do CROSS JOIN’s work when you are working with tables that have multiple columns. Do we need to include an ON statement to determine the column we want to use for the cross join. (This is just my thought, considering we’ve not had to use the ON statement with CROSS JOIN’s so far).

Thanks!

“Suppose we wanted to know how many users were subscribed during each month of the year.”
So, for newspaper I can use the following query:

SELECT start_month, COUNT(id)
FROM newspaper
GROUP BY 1
ORDER BY 1 ASC;

Is there any real-world use in applying the months table logic, or it’s just for understanding the cross join part?

It’s was very hard to understand if you aren’t english speaker.
Because for russian man ‘during’ sounds here like it was started from 1 to 31 march.
I spend a bit time to got idea this mean ‘was on subscribe during march’

4 Likes

Guys, I’ve read through the list of questions, but have been unable to figure out where my code is incorrect. Can someone just give me the correct answer so I can get past this exercise?

here is my code:

SELECT month, COUNT (*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= 3 AND end_month >= 3
GROUP BY month;

You’re still hard-coding “3” as the month to check. Generalize this since you’re Grouping By month. Does that help?

Yes, very much. Thank you.