Apabila mendapatkan data daripada berbilang jadual, cara membuat pertanyaan berdasarkan entri dalam jadual dalam kumpulan SQL

Question

Saya ada dua meja

1. Klik 2.ride

Klik jadual

id |. masa

Jadual Perjalanan

id |. cap masa

Saya ingin mendapatkan data daripada dua jadual KUMPULAN MENGIKUT EKSTRAK(DAY FROMride.timestamp) Tetapi saya akan mendapat data hanya jika saya mempunyai entri dalam kedua-dua jadual untuk hari yang sama, tetapi saya memerlukan data itu tanpa mengira sama ada kedua-dua jadual tidak mempunyai data. Saya tidak tahu sama ada OUTER join adalah jawapan tetapi mysql tidak menyokong OUTER JOIN

Pertanyaan semasa saya hanya mendapat data jika terdapat entri dalam kedua-dua jadual

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)```

Answer 1

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