I can't join tables correctly in a query, how can I fix it?

Question

MariaDB

Hello everyone, I can not correctly join the tables in the query.
we have tables of the same type with the values ​​tddoppu, tddopsredn, tddopprochee
and a table with the date tddate
, it is necessary to select all the values ​​from these tables, which are also missing, by iddate and counterid.

SELECT `tddoppu`.`value`       as `tddoppu`,
       `tddopsredn`.`value`    as `tddopsredn`,
       `tddopprochee`.`value`  as `tddopprochee`,
       `tddate`.`iddate`       as `iddate`,
       `tddoppu`.`counterid`   as `counterid`
from `tddate`
         left join `tddoppu`
                   on `tddate`.`iddate` = `tddoppu`.`dateid`
                       and `tddoppu`.`counterid` in (:COUNTER_IDS)
         left join `tddopsredn`
                   on `tddate`.`iddate` = `tddopsredn`.`dateid`
                       and `tddopsredn`.`counterid` in (:COUNTER_IDS)
         left join `tddopprochee`
                   on `tddate`.`iddate` = `tddopprochee`.`dateid`
                       and `tddopprochee`.`counterid` in (:COUNTER_IDS)

where `tddate`.`iddate` = :DATE_ID

As a result, I get duplicate lines.

tddoppu,tddopsredn,tddopprochee,iddate,counterid
170.86, null, 148.14, 27, 9981
28.33, null, 148.14, 27, 9988
170.86, null, 21.67, 27, 9981
28.33, null, 21.67, 27, 9988

I understand that I need to somehow specify in the query for equality in the counterid tables, but if I do
if I add to WHERE

and tddoppu.counterid = tddopsredn.counterid
and tddoppu.counterid = tddopprochee.counterid
and tddopprochee.counterid = tddopsredn.counterid

rows are not selected

Answer 1

JOIN is a Cartesian product . If in the first and third tables you have two values ​​in the selection, (170.86, 28.33) and (148.14, 21.67) respectively, then you will get four rows (2 * 2) as a result. For each value from the first set, each value from the second set will be added.