Mysql: Select rows from a table that are not in another

Databases
1

Hi there, I hope in your help.

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 ?

What are these 61 rows more ?

Can you please help me figure out the problem?

Thanks in advance.

3

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
1 Like
4

Hi r937, thank you so much for reply.

I have tried your suggested query and actually there are duplicates rows in the table.

I not want manually delete duplicates rows them one by one but how to do for excluding them from sql query ?

Thank you in advance,
Best regards
Antonio

5

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)

1 Like
6

Thank you.

This is example of duplicates rows on dotable2 :

7

which columns from dotable2 do you need to include in the join query?

8

The colums in doTable2 are doCode and doElement.

Thanks

9

okay

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

10

Thank you so much !

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.