1

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!

Mc Manuel
  • 105
  • 8

1 Answers1

2

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)

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • No, sorry if I wasn't clear but the keyword can match with name, category and merchant, but it was the INNER JOIN was missing in my request ! Thanks a lot ! – Mc Manuel Jul 05 '18 at 15:10
  • If you want to understand the different joins, [this old SO post](https://stackoverflow.com/questions/38549/) has been popular. Although, it's mostly just INNER JOIN and LEFT JOIN that are used the most. – LukStorms Jul 05 '18 at 15:19