Getting information from different tables in an database

Question

I have two tables:

       User
---------------------
id        primary key 
name      varchar 
mobile    varchar

Sample data: (1, airtel, '9887456321,6985326598,88523695874')

         Client
---------------------------
id              primary key
clientname      varchar 
mobileno        varchar 

Sample Data: (1,John Doe, 9887456321)

I want to search the client name from first table on the basis of mobile field value.

If mobile field has values(9887456321,6985326598,88523695874) then how do I know which client name that particular mobile phone number belongs to?

Answer 1

You say the mobile field has values such as 9887456321,6985326598,88523695874? If so you should normalise your database (i.e. have a new table with columns id and mobile and one row per mobile number.)

Without this you will need to do

WHERE ','+mobile + ',' LIKE '%,6985326598,%'

which is pretty inefficient.

Answer 2

There has to be a matching column between your client table and the other table. It is not clear from your description what that column is. From what you have said, I believe your schema looks like:

Create Table MobileNumbers
(
    Id ... not null Primary Key
    , Name varchar(??) not null
    , Mobile varchar(??) not null
)

Create Table Client
(
    Id ... not null Primary Key
    , ClientName varchar(??) not null
    , MobileNo varchar(??) not null
)

Assuming that Client.Mobile matches MobileNumbers.Name, we have:

Select ...
From Client
    Join MobileNumbers
        On MobileNumbers.MobileNo = Client.Mobile
Where Client.Mobile In('9887456321','6985326598','88523695874')

In this structure, I'm assuming that each Mobile value contains a single number and not a comma-delimited list of numbers. If it is actually the case that the a single cell can contain multiple values (please edit your original post is that is the case), then as Martin Smith mentioned, you need to normalize the data.

EDIT

Given your revision of the OP, the root problem is that you have multiple values in a single column. The right solution would be to normalize the mobile numbers into a second table:

Create Table UserMobileNumber
(
    UserId ... not null References User ( Id )
    , Carrier varchar(??) not null
    , Mobile varchar(??) not null
    , Constraint UC_UserMobileNumber Unique ( Mobile )
)

Then the solution becomes trivial:

Select ...
From User
    Join UserMobileNumber
        On UserMobileNumber.UserId = User.Id
Where UserMobileNumber.Mobile In('9887456321','6985326598','88523695874')

However, in the interim, what you need is a split function:

Create Function dbo.udf_Split
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
Returns Table
As
Return
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP (Len(@DelimitedList) + 2) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

Now you can do something like:

Select ...
From User
    Outer Apply dbo.Split( User.Mobile ) As M
    Join Client
        On Client.MobileNo = M.Value
Where M.Value In('9887456321','6985326598','88523695874')