CODEWITHSUNDEEP
mysqlsqldatabase
Nordine Khilifi
Nordine Khilifi

Reputation: 25

Count dismatch between 2 rows from 2 different tables - MySQL

I have a database with 2 tables. table1 and table2.

Table1 contains a list of tests ( column 'name' ). And I want to count how many rows are missing in table2 ( this table has also a column 'name' ) according to the list of tests of table1. So I just want to count the mismatch between table1.name and table2.name.

I tried several querys, but all didnt really work. I tried to use the 'NOT IN' statement but it takes too much time. Like several minutes.

For example, the output should be :

COUNT(*) = 20

It means that 20 tests are missing ( or not done yet ) in table2.

I'm using MySQL, so I can't use EXCEPT or MINUS statement.

Thank you by advance.

Nordine

Upvotes: 0

Views: 54

Answers (3)

Fahmi
Fahmi

Reputation: 37473

Try the below query:

select count(case when bname is null then 1 end)
from
(
select a.name as aname, b.name as bname from 
table1 a left join table2 b
on a.name=b.name)x

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use not exists :

select count(*)
from table1 t1
where not exists (select 1 from table2 t2 where t2.name = t1.name);

If you have a duplicate name in table1 then you need count(distinct t1.name) instead.

Upvotes: 1

Rahul Jain
Rahul Jain

Reputation: 1399

MINUS can be used in MySQL. Ref:http://www.mysqltutorial.org/mysql-minus/

Try this:

SELECT name
FROM table1
MINUS
SELECT name
FROM table2

Upvotes: 0

Related Questions