Get Values not in the second table using find_in_set

Question

I have two tables and i need to get list of all store_ids that are not in the other table

BusinessUnit Table   User Table
StoreId(varchar)     StoreId(varchar)
1                    1,2
2                    3,4
3                    1,5
4                    4,6
7                    4

How to get values of storeid 5,6 which are not present in the business unit table but are present in the user Table? Tried to use several using find_in_set and nothing works.

Do you know the full range (min and max) of possible id values? — Uueerdo, May 14 '15 at 16:52
These are not actual data.Storeids are like '000001' and goes till '005000'. — Learner2011, May 14 '15 at 16:54
Do you know the maximum length of `User.StoreId`? There's no easy way to split up a comma-separated value unless you know how many elements it can have. — Barmar, May 14 '15 at 16:56
This is one of the many reasons why you should normalilze the data instead of storing a CSV. — Barmar, May 14 '15 at 16:56
@Barmar csv values in storeid in the user table - max would be 6 — Learner2011, May 14 '15 at 16:59

Uueerdo · Answer 1 · 2015-05-14T17:15:36.753

0

IF you know all the possible values (and the number of them is reasonably manageable) you can populate a new table with them (you can make it TEMPORARY or just DROP it afterwards), and do this

SELECT * 
FROM (
      SELECT allIDs.Id 
      FROM allIDs 
         INNER JOIN `User` AS u 
         -- ON CONCAT(',', u.StoreID, ',') LIKE CONCAT('%,', allIDs.Id, ',%')
         ON FIND_IN_SET(allIDs.Id, u.StoreID)
   ) AS IDsInUserTable
   LEFT JOIN `BusinessUnit` AS b ON IDsInUserTable.Id = b.StoreID
HAVING b.StoreID IS NULL
;

In this example, allIDs is the aforementioned "possible values" table.

edited May 14 '15 at 17:15

answered May 14 '15 at 17:05

Uueerdo

15,723
1
16
21

Why not use `FIND_IN_SET` instead of all that CONCAT and LIKE stuff? – Barmar May 14 '15 at 17:07
@Barmar because he is trying to find values in the csv field in Users that are not in the BusinessUnit table, not values in the BusinessUnit table that are or are not in the Users table's csv field. – Uueerdo May 14 '15 at 17:12
I mean when you're joining between `AllIDs` and `User`. – Barmar May 14 '15 at 17:12
`ON FIND_IN_SET(allIDs.Id, u.StoreId)` – Barmar May 14 '15 at 17:13
Oh, haha, true that would probably be better; I've never actually had use for `FIND_IN_SET` so it doesn't spring to mind to use it. – Uueerdo May 14 '15 at 17:14
You're probably smart enough not to put CSV in your tables, so you don't need it much. – Barmar May 14 '15 at 17:15
I'd like to think so, but I am sure have done equivalently bad stuff in other areas. Everyone has their strengths and weaknesses. – Uueerdo May 14 '15 at 17:19

Barmar · Answer 2 · 2015-05-14T17:14:28.493

Use SUBSTRING_INDEX to get all the values from the CSV field. Since there can be up to 6 IDs in the CSV, you need to call it once for each position.

SELECT u.StoreId
FROM (
    select substring_index(StoreId, ',', 1) AS StoreID
    FROM User
    UNION
    select substring_index(substring_index(StoreId, ',', 2), ',', -1)
    FROM User
    UNION
    select substring_index(substring_index(StoreId, ',', 3), ',', -1)
    FROM User
    UNION
    select substring_index(substring_index(StoreId, ',', 4), ',', -1)
    FROM User
    UNION
    select substring_index(substring_index(StoreId, ',', 5), ',', -1)
    FROM User
    UNION
    select substring_index(substring_index(StoreId, ',', 6), ',', -1)
    FROM User) AS u
LEFT JOIN BusinessUnit AS b ON u.StoreId = b.StoreID
WHERE b.StoreId IS NULL

DEMO

Get Values not in the second table using find_in_set

2 Answers2