0

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?

Alberto Moro
  • 1,014
  • 11
  • 22
user3095854
  • 33
  • 4
  • 1
    what about IFNULL(trade.name,'') ? – MGorgon Feb 13 '20 at 15:23
  • Can you explain how the query should be then? – user3095854 Feb 13 '20 at 15:42
  • just replace trade.name with IFNULL statement – MGorgon Feb 13 '20 at 15:45
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Feb 13 '20 at 23:49
  • This is not clear about what the problem is or what the desired behaviour is. Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) [ask] For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. – philipxy Feb 13 '20 at 23:54

1 Answers1

0

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

Alberto Moro
  • 1,014
  • 11
  • 22