Search code examples
mysqlnullasp-classicsql-like

MySQL - Select with LIKE and Nulls

I need to inform a list of clients and they agents.

But many clients didn't had an agent. (and the adm will insert the data)

Then my SELECT retuns:

=============
CLIENT - AGENT
ABC INC - MARK
DEF INC - MARK
GHI INC - JOHN
YXZ INC - NULL
---------------

The ADM have a "Search" engine to select only clients for some agent.

and my Select is:

SELECT 
CLIENTS.ID AS CLIENT, CLIENTS.NAME AS NAME, AGENTS.NAME AS AGENT FROM CLIENTS  
LEFT JOIN AGENTS ON  AGENTS.CLIENTID = CLIENTS.ID  
WHERE CLIENTS.NAME LIKE '%"&CLIENTNAME&"%' AND AGENTS.NAME LIKE '%"&AGENTNAME&"%'"

When I use LIKE %% they bring ALL AGENTS. When I use LIKE %MARK% they bring ALL AGENTS WITH MARK

but.. with LIKE %% I dont had the "NULL" Agents..

And I need this info too..

any idea to bring the NULL?

I'm working with Classic ASP.

Solution

  • You need to add something like below:

    ....WHERE IFNULL(CLIENTS.NAME,'') LIKE '%%'...