I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and print_issues.pub_date would be returned.
SELECT print_issues.pub_date, print_issues.x, s.page_size FROM print_issues, ( select pub_date, page_size from print_page_size, print_issues where effective_date < print_issues.pub_date order by effective_date desc ) as s WHERE s.pub_date = print_issues.pub_date group by print_issues.pub_date order by print_issues.pub_date desc
The query is an artifact of the schema of
print_page_size. That table has the columns
effective-date. New entries with a greater
effective_date supersede old ones. The goal of the query is to unite the
print_issue table with the
page_size in effect when each issue was published.
Sample Data: TABLE: print_page_size ====================== effective_date page_size 2014-01-01 100 2017-05-01 105 TABLE: print_issues =================== pub_date x 2017-04-26 "Random" 2017-05-02 "Data" OUTPUT ====== print_issues.pub_date print_issues.x s.page_size 2017-04-26 "Random" 100 2017-05-02 "Data" 105
My questions: Is there a problem with this approach? Is there a more direct way to to achieve the desired result?