search in sql in multiple column if one is empty then get from another

Question

WITH FilteredClient AS
(
    SELECT 
        Client.ClientId, Client.PersonId, Client.ClientCompanyId, 
        Client.ClientCategoryId, Client.IsImported, Client.IsModified, 
        Client.StatusId, Client.ClientTypeId, Client.LeadSourceId, 
        Person.Company, Person.FirstName, Person.MiddleName, 
        Person.LastName, Person.AddressLine1, Person.AddressLine2, 
        Person.AddressLine3, Person.City, Person.State, 
        Person.Country, Person.Zip, 
        Person.HomePhone, Person.WorkPhone, Person.CellPhone, 
        Person.Fax, Person.EmailId, Person.Website, 
        ClientStatus.ClientStatusName, 
        ClientCategory.ClientCategoryName, 
        ClientType.ClientTypeName, LeadSource.LeadSourceName,
        [User].Username, Client.AddedBy, [User].UserId
    FROM 
        Client 
    INNER JOIN
        ClientCategory ON Client.ClientCategoryId = ClientCategory.ClientCategoryId 
    INNER JOIN
        Person ON Client.PersonId = Person.PersonId
    INNER JOIN 
        ClientType ON Client.ClientTypeId = ClientType.ClientTypeId 
    INNER JOIN
        ClientStatus on Client.StatusId = ClientStatus.ClientStatusId  
    INNER JOIN
        LeadSource on Client.LeadSourceId = LeadSource.LeadSourceId 
    INNER JOIN
        [User] ON [User].UserId = Client.AddedBy
    WHERE     
        (Client.CompanyId = 1
        AND ClientCategory.ClientCategoryName LIKE ('%%')
        AND ClientType.ClientTypeName LIKE ('%%')
        AND ClientStatus.ClientStatusName LIKE ('%%')
        AND LeadSource.LeadSourceName LIKE ('%%')
        AND (Person.FirstName + '' + Person.MiddleName + '' + Person.LastName) LIKE ('%Tom%')
        AND person.Company LIKE ('%Tom%'))
)
SELECT
    ClientId, PersonId, ClientCompanyId, ClientCategoryId,
    IsImported, IsModified, StatusId, ClientTypeId, LeadSourceId, 
    Company, FirstName, MiddleName, LastName, 
    AddressLine1, AddressLine2, AddressLine3, 
    City, [State], Country, Zip, 
    HomePhone, WorkPhone, CellPhone, Fax, EmailId, Website, 
    ClientStatusName, ClientCategoryName, ClientTypeName,
    LeadSourceId, LeadSourceName, Username, AddedBy, UserId
FROM 
    FilteredClient

Now I searched according to ClientName (client name includes CompanyName and person's first name). Both have seperate column in Person table.

I put text in textbox then click on search then the value of textbox goes to both company.person and person.firstname .

Now I want the search result to come from both column but if there is some match found in companyname then it pick companyname and related person name but if i put person name in textbox then in if company column has 0 result then it is not searched in person firstname

Please tell me if any mistake in my condition

Please help me

Answer 1

You are using AND clause in where condition its a mistake as per your requirement.You should use OR clause instead of AND so replace your query with following

AND ((Person.FirstName +''+Person.MiddleName +''+Person.LastName) like ('%Tom%')
OR person.Company like('%Tom%'))

Answer 2

take a look at this answer. this may help you tune your algorithm and adapt your requirement on it: This Link