SQL Multiple Tables Cross Join Q3

Here is the link to my issue. SQL Multiple Tables Section 7 Instruction 3.
https://www.codecademy.com/courses/learn-sql/lessons/multiple-tables/exercises/cross-join?action=resume_content_item

Code I’m using is below, please let me know if it’s flawed in some way.

Q3 makes no sense to me in the WHERE statement. The hint says to look at the month column and you’ll see that some are gone now. I did it and I don’t understand why some are missing.

The way it reads to me is this: you have start_month which can be anything between 1 and 12. And you have the month column which can be between 1 and 12 as well. In the FROM and AND statement below, there is no numerical limiter to narrow results down. It reads more like: if start_month = 12 and month = 1, it’s not going to show it in the result because 12 is not less than or equal to 1.

Sorry, it’s hard to explain this clearly. It might be best if someone just explains Q3 in painstaking detail to me :frowning: I was acing it until this lesson and now I hit a hard wall for two days in a row now.

SELECT *
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month;

1 Like

You are correct, but this reduces the number of records from the CROSS JOIN.

Cross join works like this:
(These are fictional tables)

Table: subscription

id; start_month; end_month
1; 1; 3
2; 10; 12

Table: months

month; name
1; January
2; February
3; March
...

After the cross join it looks something like this:

id; start_month; end_month; month; name
1; 1; 3; 1; January
1; 1; 3; 2; February
1; 1; 3; 3; March
...
2; 10; 12; 1; January
2; 10; 12; 2; February
2; 10; 12; 3; March
...

So like you said, if start_month is larger than month like this line:

2; 10; 12; 1; January

Then it will be excluded from the results because of the where, but these will remain:

2; 10; 12; 10; October
2; 10; 12; 11; November
2; 10; 12; 12; December
2 Likes

It seems I mostly understood how it works but didn’t realize that’s what they were trying to do in the instructions. Thank you for the clarification, it was very helpful :slight_smile: