# Counting number of ids present in one table which is not presented on another for a particular user

I have two tables I want to count the number of ids presented on first which is not in second with respect to user id how can I do the same?

For example.

Case 1

Table 1.

``````|id|data           |User_id
|4 |Some data      |12
|5 |Another data   |8
|6 |Some other data|6
``````

Table2

``````|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |
``````

Case 2

Table 1.

``````|id|data           |userid
|4 |Some data      |12
|5 |Another data   |24
|6 |Some other data|32
|7 |Some more data |12
``````

Table2

``````|id|table1Id|userId|
|1 |4       |12    |
|2 |5       |14    |
|3 |12      |12    |
``````

What I am trying to do is this.

I want to know how many occurrences of id in table1 which is not in table2 for the user 12.

How can I do that?

Case 1 it should return 0

Case2 it should return 1

You can get it by using `NOT EXISTS`

First example:

``````create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 8);
insert into table1 values (6, 'some another data', 6);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);
``````
``````SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
FROM   table2
WHERE  tableid = table1.id
AND    user_id = table1.user_id);
``````
```| coincidences |
| -----------: |
|            0 |
```

dbfiddle here

Second example:

``````create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 24);
insert into table1 values (6, 'some another data', 32);
insert into table1 values (7, 'some more data', 12);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);
``````
``````SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
FROM   table2
WHERE  tableid = table1.id
AND    user_id = table1.user_id)
``````
```| coincidences |
| -----------: |
|            1 |
```

dbfiddle here

• I am seeing this query is very slow for the comparatively bigger table is there a way to rewrite the same with JOIN operations I have tried some but failed. Dec 15, 2017 at 4:00
• Which MySql version? Dec 15, 2017 at 7:55
• @AndriyM Is that in at this way Instead of this FROM table2 WHERE tableid = table1.id AND user_id = table1.user_id) This one? FROM table2 WHERE tableid,user_id = table1.id AND user_id = table1.user_id) Dec 15, 2017 at 12:24
• @McNets Mysql 5.6 Dec 15, 2017 at 12:30
• @arunwebber: No, the condition used in the answer is correct as it is. It has already been updated from `WHERE tableid = table1.id` (correlation on `tableid` only) to `WHERE tableid = table1.id AND user_id = table1.user_id` (correlation on both `tableid` and `user_id`). Dec 15, 2017 at 15:47