Need to do a conditionnal LIKE request

Question

Here I am struggling with SQL

I made a search bar that can match with three different rows in sql. Problem, one of these rows isn't in the same table as the two others.

Here is an example

 TABLE 1 : topics
 id  ||  name        ||  category || id_merchant
 1   ||  football    ||  Sports   || 1
 2   ||  marmalade   ||  cooking  || 2
 3   ||  Hitchcock   ||  cinema   || 3

 TABLE 2 : merchant
 id || merchant
 1  || NIKE
 2  || Cooking Corp
 3  || GoodFilms Corp

PROBLEM with this request (when I'm searching for "Corp" keyword) :

SELECT T.name, T.category, M.merchant 
FROM topics AS T, 
     merchant AS M 
WHERE T.name LIKE '%Corp%' 
   OR T.category LIKE '%Corp%' 
   OR M.merchant LIKE '%Corp%' 
  AND T.id_merchant = M.id

It returns all the merchant that "Corp" in there names, but I only want to retrieve a topic that have a merchant matching with "Corp"

Then I tried this :

SELECT T.name, T.category, M.merchant 
FROM topics AS T, 
     merchant AS M 
WHERE T.name LIKE '%Corp%' 
   OR T.category LIKE '%Corp%' 
   OR (SELECT M.merchant WHERE M.id = T.id_merchant) LIKE '%Corp%' 
  AND T.id_merchant = M.id

But it returns a syntax error.

Hope I was clear enough.

Thank you in advance!

Answer 1

If you just want the topics where the merchant's name has 'Corp' in it.
Then that would be the only criteria I guess?

SELECT T.name, T.category, M.merchant 
FROM topics AS T
INNER JOIN merchant AS M ON (M.id = T.id_merchant)
WHERE M.merchant LIKE '%Corp%'

Note that the JOIN syntax is used to increase readability.

Btw, I notice you like using OR's. So an advice, it's best to use parentheses when using both OR's and AND's. Because AND's are evaluated before OR's. So m OR n AND x OR y is evaluated as m OR (n AND x) OR y.

So with the other OR's included:

SELECT T.name, T.category, M.merchant 
FROM topics AS T
LEFT JOIN merchant AS M ON (M.id = T.id_merchant)
WHERE (
   M.merchant LIKE '%Corp%' OR 
   T.name LIKE '%Corp%' OR 
   T.category LIKE '%Corp%'
)

(not really needed for the sample data)
(notice that the LEFT JOIN was used this time. That's just to catch also the topics that don't even have a merchant)