I have two tables
Click table
id |Time
Ride List
id |Timestamp
I want to get data from two tables GROUP BY EXTRACT(DAY FROMride.timestamp) But I will get the data only if I have entries in both tables for the same day, but I need the data regardless of whether both tables have no data. I don't know if OUTER join is an answer but mysql doesn't support OUTER JOIN
My current query only gets data if there are entries in both tables
COUNT(distinct ride.id) AS ride_ads, COUNT(distinct clicks.id) AS clicks FROM ride INNER JOIN clicks ON EXTRACT(DAY FROM ride.timestamp)=EXTRACT(DAY FROM clicks.time) GROUP BY EXTRACT(DAY FROM ride.timestamp), EXTRACT(DAY FROM clicks.time)```
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
SELECT DATE(ride.timestamp) AS Day, COUNT(DISTINCT ride.id) AS ride_ads, COUNT(DISTINCT clicks.id) AS clicks FROM ride LEFT JOIN clicks ON DATE(ride.timestamp) = DATE(clicks.time) WHERE DATE(ride.timestamp) > NOW() - INTERVAL 15 DAY GROUP BY Day UNION SELECT DATE(clicks.time) AS Day, #selecting date from second table since I might have record in this table and I am using group by Day COUNT(DISTINCT ride.id) AS ride_ads, COUNT(DISTINCT clicks.id) AS clicks FROM ride RIGHT JOIN clicks ON DATE(ride.timestamp) = DATE(clicks.time) WHERE DATE(clicks.time) > NOW() - INTERVAL 15 DAY GROUP BY Day ORDER BY Day