1

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

3

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. – arunwebber Dec 15 '17 at 4:00
  • Which MySql version? – McNets Dec 15 '17 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) – arunwebber Dec 15 '17 at 12:24
  • @McNets Mysql 5.6 – arunwebber Dec 15 '17 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). – Andriy M Dec 15 '17 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.