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
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
);
Suggestion:
Is storing a delimited list in a database column really that bad?
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments