car
| id | brand | available |
|---|---|---|
| 1 | toyota | yes |
| 2 | nissan | yes |
| 3 | masda | no |
| 4 | tesla | yes |
purchase
| id_car | date | price |
|---|---|---|
| 1 | 2020-01 | $50 |
| 1 | 2020-02 | $52 |
| 2 | 2020-01 | $43 |
| 3 | 2020-01 | $35 |
| 3 | 2020-02 | $32 |
| 3 | 2020-03 | $15 |
| 4 | 2020-03 | $43 |
I need results like in the following table
| id | brand | available | date | price |
|---|---|---|---|---|
| 1 | toyota | yes | 2020-01 | $50 |
| 1 | toyota | yes | 2020-02 | $52 |
| 2 | nissan | yes | 2020-01 | $43 |
| 2 | nissan | yes | 2020-02 | null |
| 4 | tesla | yes | 2020-01 | null |
| 4 | tesla | yes | 2020-02 | null |
my query search must use availability and date(array)
select .....
join ....
where
car.available = 'yes'
and purchase.date in ('2020-01', '2020-02')
CodePudding user response：
We can use a calendar table cross join approach here:
SELECT c.id, c.brand, 'yes' AS available, d.date, p.price
FROM (SELECT id, brand FROM car WHERE available = 'yes') c
CROSS JOIN (SELECT DISTINCT date FROM purchase) d
LEFT JOIN purchase p
ON p.id_car = c.id AND p.date = d.date
WHERE d.date IN ('2020-01', '2020-02')
ORDER BY c.id;
The idea is to generate two sets, one for all available car brands, and the other for all known available dates. We then left join this intermediate table to the purchase table to get the output you want.