'Join on elements that arent in a table, or bringing back elements that arent in a table
I have two tables a month table and a product table. The product table will be updated in the future to have new prices (I will insert new 'valid_from' dates)
I would like to join the two tables together to return month, product, price_rate, initial_price, hire_price, other and connection under specific parameters:
I want to return months from the months table that fall within the date range as defined between valid_from and the next value of valid_from for the product and price rate, the following begins to return my required dates and columns:
SELECT
month,
product,
price_rate,
initial_price,
hire_price,
other,
connection
FROM w.products pc
RIGHT JOIN m.month m ON m.month >= pc.valid_from
However; I need to bring back all months from the months table where valid_from is null in the products table, prior to the instance of the next valid from (for the same product and price rate)
I also want to bring back the connection value - how can I do this/what do I join on as I'm currently joining on date, but this doesn't exist within the row where there is a value for connection
| id | product | price_rate | valid_from | initial_price | hire_price | other | connection |
|---|---|---|---|---|---|---|---|
| 1 | computer | 100 | 154.75 | 115.5 | 0.015 | ||
| 2 | computer | 100 | 01/01/2021 | 154.75 | 115.5 | 0.015 | |
| 3 | computer | 1000 | 154.75 | 135 | 0.015 | ||
| 4 | computer | 1000 | 01/01/2021 | 154.75 | 135 | 0.015 | |
| 5 | computer | 10000 | 01/01/2020 | 453.41 | 345.5 | 0.015 | |
| 6 | mouse | 100 | 154.75 | 142.5 | 0.015 | ||
| 7 | mouse | 100 | 01/01/2021 | 154.75 | 142.5 | 0.015 | |
| 8 | mouse | 1000 | 01/01/2020 | 154.75 | 162 | 0.015 | |
| 9 | mouse | 10000 | 01/01/2020 | 450.91 | 415 | 0.015 | |
| 10 | keyboard | 100 | 163.08 | 142.5 | 0.015 | ||
| 11 | keyboard | 100 | 01/01/2021 | 163.08 | 142.5 | 0.015 | |
| 12 | keyboard | 1000 | 01/01/2020 | 163.08 | 162 | 0.015 | |
| 13 | 121 |
| month |
|---|
| 01/01/2019 |
| 01/02/2019 |
| 01/03/2019 |
| 01/04/2019 |
| 01/05/2019 |
| 01/06/2019 |
| 01/07/2019 |
| 01/08/2019 |
| 01/09/2019 |
| 01/10/2019 |
| 01/11/2019 |
| 01/12/2019 |
| 01/01/2020 |
| 01/02/2020 |
| 01/03/2020 |
| 01/04/2020 |
| 01/05/2020 |
| 01/06/2020 |
| 01/07/2020 |
| 01/08/2020 |
| 01/09/2020 |
| 01/10/2020 |
| 01/11/2020 |
| 01/12/2020 |
| 01/01/2021 |
| 01/02/2021 |
| 01/03/2021 |
| 01/04/2021 |
| 01/05/2021 |
| 01/06/2021 |
| 01/07/2021 |
| 01/08/2021 |
| 01/09/2021 |
| 01/10/2021 |
| 01/11/2021 |
| 01/12/2021 |
| 01/01/2022 |
| 01/02/2022 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|