Search code examples
mysqljoinnullconcatenation

MySQL problem with query with LIKE on a NULL value

I have two tables: articles and trade. They are left join on articles.tradeid = trade.id. Not every article has a trade. So there is the value of articles.tradeid 0.

When I start a search like:

SELECT * FROM articles 
LEFT JOIN trade ON articles.tradeid=trade.id 
WHERE CONCAT(articles.number,'|',trade.name) LIKE '%12345%'

I do not get any results although there is an article with number 12345, but it has a tradeid of 0. Because of the join the value of trade.name is NULL. Anyone a solution?

Solution

  • You can replace CONCAT with CONCAT_WS:

    SELECT * 
FROM articles 
LEFT JOIN trade ON articles.tradeid=trade.id 
WHERE CONCAT_WS('|', articles.number, trade.name) 
LIKE '%12345%'

    DEMO

    Concatenate With Separator is a special form of CONCAT. Be careful because it has a different syntax