Search a key word in the whole column of that table

Question

I have two tables.

User table

UserId    Username
 1         User1
 2         User2
 3         User3
 4         User4
17         User17
18         User18
20         User20

And One more

Customer-Support table

CSid  Userslist
 1     1,3
 2     2
 3     20,17,18

How can I get the userids of the User Table are not present in Customer-Support Table..

I would suggest you start with normalizing your database and getting rid of comma-separated values in any field. Your question will then be very easy to answer. — oerkelens, Sep 17 '14 at 08:38
@jpw : Thanks for the reply..This is the older db .. This already has millions of records in this table.. So i cant change the table structures now.. :( — Pravin Kumar, Sep 17 '14 at 08:41
Thanks for the reply @oerkelens ..This is the older db .. This already has millions of records in this table.. So i cant change the table structures now.. :( — Pravin Kumar, Sep 17 '14 at 08:42
@PravinKumar does [this](http://vevlo.blogspot.in/2013/12/mysql-query-for-finding-values-in-comma.html) help you — Ankur Singhal, Sep 17 '14 at 08:43
Millions of records is not much. You only need to add one field to your Customer table, and you will have a lot of benefits. You can throw away all the useless code that involves splitting up the crazy UsersList field. No matter how old this DB is, that field should never have existed. The fact that somebody was incompetent enough to create it is no reason to let it survive. — oerkelens, Sep 17 '14 at 08:50

score 3 · Answer 1 · answered Sep 17 '14 at 08:46

The question is answerable with the current data-model, but it is a lot of work, complicated, and a sheer waste of time.

However, with a sensible data model, it is a very simple question, so I will answer it with such a model.

We remove the UsersList field from your Customer-Support table. It should never never never be there. No, really, never.

Now, assuming that, as your example data shows, every user can have one CSid, we will add a field CSid to your User Table. This is called a foreign key. Since, as you mention, not all users are linked to the CS Table, you make sure the field allows NULL values.

Now we fill in the data:

User table

UserId    Username  CSid
 1         User1     1
 2         User2     2
 3         User3     1
 4         User4    NULL
17         User17    3
18         User18    3
20         User20    3

And now, to answer your question:

SELECT * FROM UserTable WHERE CSid IS NULL;

Your question is a very good example why it pays to think about your data model before messing it up. Your query is extremely simple, if your data model makes sense.

_it is a lot of work, complicated, and a sheer waste of time_ I disagree. — jpw, Sep 17 '14 at 09:07

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

Using a recursive common table expression to split the values should do it:

;WITH Split AS
(
    SELECT
          LEFT(Userslist,CHARINDEX(',',Userslist)-1) AS Userslist
            ,RIGHT(Userslist,LEN(Userslist)-CHARINDEX(',',Userslist)) AS Remainder
        FROM CustomerSupport
        WHERE Userslist IS NOT NULL AND CHARINDEX(',',Userslist)>0
    UNION ALL
    SELECT
       Userslist AS Userslist, NULL AS Remainder
    FROM CustomerSupport
    WHERE Userslist IS NOT NULL AND CHARINDEX(',',Userslist)=0
    UNION ALL
    SELECT
       LEFT(Remainder,CHARINDEX(',',Remainder)-1)
        ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
    FROM Split
    WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        Remainder,null
    FROM Split
    WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)

SELECT * FROM Users 
WHERE UserId NOT IN (
SELECT Userslist FROM Split)

The query will return User4 for your sample data as it's the only one missing in the CustomerSupport table.

Sample SQL Fiddle

I adapted my answer from this answer by KM. Credit to the one who deserves it.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 17 '14 at 09:02

jpw

44,361
6
66
86

It is interesting how your answer shows very clearly how the largest, most complicated part, deals with taming the data model, and then a small, simple and quick query in the end is what actually gets the desired answer out of the cleared-up mess :) – oerkelens Sep 17 '14 at 09:26
@oerkelens I agree, dealing with a flawed model is the complicated part, but sometimes we just have to play with the cards we were dealt ;) – jpw Sep 17 '14 at 09:34
@oerkelens also, what I disagreed with on your answer wasn't the solution per se, but rather the waste of time part. I did +1 for the content, even if it might not be applicable in this case. :) – jpw Sep 17 '14 at 09:37
Np, I understood that when reading your answer :). I still think it's a waste of time, but if the client pays for the waste, why complain? I have implemented many bad solutions because "that is how we want it" :) (And a waste of time can still be interesting of fun to do!) – oerkelens Sep 17 '14 at 09:39

Search a key word in the whole column of that table

2 Answers2