how to display date in 2 tables then use where between date [MYSQL]-CodePudding

this is my first time accessing stackoverflow, i have a problem in my query

this query

SELECT t1.DateA, t2.DateB
FROM tbl_date_a  t1
LEFT JOIN
(
    SELECT DateB
    FROM tbl_date_b  
    GROUP BY DateB
) t2
ON t1. DateA = t2.DateB
WHERE t1.DateA OR t2.DateB BETWEEN '2022-09-06' AND '2022-09-08'
GROUP BY t1.DateA

this my query results image results

the problem is, when i add a data in tbl_date_b with DateB 2022-09-08, but it doesn't show up in results view, but if i add data in tbl_date_a with DateA 2022-09-08. then the data will appear. i know the problem is in left join because it is used in tbl_date_a. . how to find a solution in this problem?

the result expected is like this image expected

thank you very much for the help, I hope this problem is resolved quickly

Solve Query By FrankSchmitt

CodePudding user response：

It seems you need a FULL OUTER JOIN.

UPDATE Since MySQL doesn't support this, you can emulate it by using two LEFT JOINs and a UNION:

SELECT t1.DateA, t2.DateB
FROM tbl_date_a  t1
LEFT OUTER JOIN
(
    SELECT DateB
    FROM tbl_date_b  
    GROUP BY DateB
) t2
ON t1.DateA = t2.DateB
WHERE t1.DateA BETWEEN '2022-09-06' AND '2022-09-08'
GROUP BY t1.DateA

UNION 

SELECT t1.DateA, t2.DateB
FROM 
(
    SELECT DateB
    FROM tbl_date_b  
    GROUP BY DateB
) t2

LEFT OUTER JOIN tbl_date_a  t1
ON t1.DateA = t2.DateB
WHERE t2.DateB BETWEEN '2022-09-06' AND '2022-09-08'
GROUP BY t2.DateB

SQL Fiddle

CodePudding user response：

To achieve the result you want you have to use FULL OUTER JOIN, because T1 don't have the key 2022-09-08 to make a LEFT JOIN. and add a explicit condition to T1 inside WHERE.

And one good advice is to use parenthesis when working with "OR"

So your query shoud be like this:

SELECT t1.DateA, t2.DateB
FROM tbl_date_a  t1
FULL OUTER JOIN
(
    SELECT DateB
    FROM tbl_date_b  
    GROUP BY DateB
) t2
ON t1. DateA = t2.DateB
WHERE (t1.DateA BETWEEN '2022-09-06' AND '2022-09-08') OR (t2.DateB BETWEEN '2022-09-06' AND '2022-09-08')
GROUP BY t1.DateA