With this query I find all rows in dotable1 where first four months of this year :
SELECT
CB.*
FROM
`dotable1` CB
WHERE
CB.doType IN ('A')
AND MONTH (CB.doDate) BETWEEN 1
AND 4
ORDER BY
CB.doNumber DESC;
And in output I have : 9402 rows in set
With this join query I find the rows from a table dotable1 that are in another table dotable2:
SELECT * FROM
`dotable1` CB
JOIN `dotable2` A ON A.doCode = CB.doCode
AND RIGHT (A.doElement, 1) = CB.doElement
WHERE
CB.doType IN ('A')
AND MONTH (CB.doDate) BETWEEN 1
AND 4
ORDER BY
CB.doNumber DESC;
And in output I have : 568 rows in set
Now i need select rows from a table dotable1 that are not in another dotable2 and try this query :
SELECT * FROM
`dotable1` d
WHERE
NOT EXISTS (
SELECT
NULL
FROM
`dotable2` i
WHERE
i.doCode = d.doCode
AND RIGHT (i.doElement, 1) = d.doElement
)
AND d.doType IN ('A')
AND MONTH (d.doDate) BETWEEN 1
AND 4
ORDER BY
d.doNumber DESC;
And in output I have 8895 rows in set.
My question is :
If 9402 rows - 568 rows is equal to 8834 rows why in the output with select rows from a table dotable1 that are not in another dotable2 I have 8895 rows in set ?
this is a modification of your join query, designed to show you where the dupes are coming from –
SELECT CB.doCode
, COUNT(*) AS joined_rows
FROM dotable1 CB
INNER
JOIN dotable2 A
ON A.doCode = CB.doCode
AND RIGHT(A.doElement, 1) = CB.doElement
WHERE CB.doType IN ('A')
AND MONTH(CB.doDate) BETWEEN 1 AND 4
GROUP
BY CB.doCode
HAVING COUNT(*) > 1
depends… which columns from dotable2 do you need to include in the join query? keep the GROUP BY and put those columns into aggregate functions, e.g. SUM(dotable2.foo) or AVG(dotable2.bar)
SELECT CB.doCode
, MAX(A.doElement) AS max_doElement
FROM dotable1 CB
INNER
JOIN dotable2 A
ON A.doCode = CB.doCode
AND RIGHT(A.doElement, 1) = CB.doElement
WHERE CB.doType IN ('A')
AND MONTH(CB.doDate) BETWEEN 1 AND 4
GROUP
BY CB.doCode