Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Conditional Cross Join - Why doesn't this work on the 'ON' clause?
CREATE TABLE user_tbl AS SELECT * FROM ( VALUES ('u9876',201504,201610) ,('u5564',201602,201612) ,('u4435',201606,NULL) ) AS t(user_id, start_yearmonth, end_yearmonth); CREATE TABLE date_range AS SELECT * FROM ( VALUES (201601),(201602),(201603),(201604),(201605),(201606),(201607),(201608),(201609),(201610),(201611),(201612),(201701),(201702) ) AS t2(yearmonth); -- Check user_tbl output SELECT * FROM user_tbl ; -- Check date_range output SELECT * FROM date_range ; -- SUCCESSFUL CROSS JOIN SELECT * FROM user_tbl CROSS JOIN date_range WHERE user_tbl.start_yearmonth <= date_range.yearmonth -- EXAMPLE: FOR u5564, 201601 IS NOT <= 201602, SO IT IS NOT RETURNED AND (user_tbl.end_yearmonth >= date_range.yearmonth -- EXAMPLE: FOR u5564, 201701 IS NOT >= 201702, SO IT IS NOT RETURNED OR user_tbl.end_yearmonth IS NULL) -- EXAMPLE: FOR u4435, NO ROWS RETURNED UNLESS NULL VALUES ARE ALLOWED TO BE CROSS JOINED ORDER BY user_tbl.user_id, date_range.yearmonth ; -- UNSUCCESSFUL CROSS JOIN - WHY WON'T THIS WORK IN THE 'ON' CONDITION? SELECT * FROM user_tbl CROSS JOIN date_range ON user_tbl.start_yearmonth <= date_range.yearmonth AND (user_tbl.end_yearmonth >= date_range.yearmonth OR user_tbl.end_yearmonth IS NULL) ORDER BY user_tbl.user_id, date_range.yearmonth
run
|
edit
|
history
|
help
0
1075-1077 Project Analysis
Movie Rating SQL Analysis Project
JsonB Query
Select the latest id from last week from each different contact without repeating barcodes .....
PosgresSQL Sandbox: Legitimate Resale
Assignment 1(SET A)
librarayv2
Query integer in text field - Seemingly doesn't work as expected
Black Wall
1127. User Purchase Platform