Fetch rows in MySQL which are not present in both columns from another table

Question

There are two tables with details as mentioned below in MySQL:

TABLE 1:

+-------------+---------------+
 | user_id     |  isactive     |
 +-------------+---------------+
 | aaa         | 0             |
 +-------------+---------------+
 | bbb         | 0             | 
 +-------------+---------------+

TABLE 2:

 +-------------+---------------+-----------+
 |store_no     |  owner        | store     |
 +-------------+---------------+-----------+
 |1234         | aaa           | aaa,xyz   |
 +-------------+---------------+-----------+
 |1006         | aaa           | aaa       | 
 +-------------+---------------+-----------+
 |1005         | ccc           | www       | 
 +-------------+---------------+-----------+

I need to fetch rows from table 1 whose entries are neither in 'owner' nor in 'store' column of table 2. For example, in the above scenario, the resultset should contain 'bbb'. I tried using find_in_set, locate etc but could not fetch the details as required. Please help..

Updated Tables format

Query:

select a.user_id from table1 u
left outer join table2 a
on (owner=user_id or concat(',',store,',') like concat('%,',user_id,',%'))
where (find_in_set(user_id,owner) = 0 or find_in_set(user_id,store) = 0)
and  isactive=0

FYI, store column can have concatenated values of more than one userid

select a.user_id from table1 u left outer join table2 a on (owner=user_id or concat(',',store,',') like concat('%,',user_id,',%')) where (find_in_set(user_id,owner) = 0 or find_in_set(user_id,store) = 0) and isactive=0 — Harika B, Jun 27 '16 at 12:03

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

You can try using NOT EXISTS

SELECT 
T1.user_id
FROM TABLE_1 T1
WHERE NOT EXISTS (
    SELECT 1 
      FROM 
    TABLE_2 T2
    WHERE T2.owner = T1.user_id OR FIND_IN_SET(T1.user_id,T2.store) > 0
);

SQL FIDDLE DEMO

Suggestion:

Is storing a delimited list in a database column really that bad?

edited May 23 '17 at 12:22

Community

1
1

answered Jun 27 '16 at 12:11

1000111

13,169
2
28
37

Sounds good. You can also check the fiddle that I've added just now. – 1000111 Jun 27 '16 at 12:15
@HarikaB It may 'solve the issue', but it's hardly a solution. – Strawberry Jun 27 '16 at 12:26

score 0 · Answer 2 · answered Jun 27 '16 at 12:06

0

You can do it like following :-

SELECT * FROM table1 WHERE user_id NOT IN (SELECT owner FROM table2) AND NOT FIND_IN_SET(user_id, (SELECT store FROM table2));

Second option :-

SELECT * FROM table1 WHERE user_id NOT IN (SELECT owner FROM table2) AND FIND_IN_SET(user_id, (SELECT store FROM table2)) = 0;

It may help you.

answered Jun 27 '16 at 12:06

Harsh Sanghani

1,666
1
14
32

can you please try this solution? – Harsh Sanghani Jun 27 '16 at 12:10
Thank you for the response. But "select store from table2" would return multiple rows..and therefore throw error – Harika B Jun 27 '16 at 12:11

score 0 · Answer 3 · answered Jun 27 '16 at 12:08

0

I think this should work:

select user_id
from table1
where user_id not in
  (select owner from table2)
and not find_in_set(user_id,
  (select store from table2)
);

answered Jun 27 '16 at 12:08

ADyson

57,178
14
51
63

1

Thank you for the response. But "select store from table2" would return multiple rows..and therefore throw error – Harika B Jun 27 '16 at 12:11

Fetch rows in MySQL which are not present in both columns from another table

3 Answers3