SEARCH DATA BETWEEN TWO TABLES FROM SQL-CodePudding

I want to get the data only from "products" table, the problem is if the data was found from "categories" table it will returns empty.

SELECT p.code AS CODE, p.model AS NAME 
FROM products p 
LEFT JOIN categories c 
ON p.category = c.id 
WHERE p.model LIKE "%LAPTOP%" OR c.category_name LIKE "%LAPTOP%"

I thought this query will return exact table column you want to achieve if both were connected from this ON p.category = c.id.

Any help?

CodePudding user response：

Assume that all your cases is as your sample data in db-fiddle, you could use this query:

SELECT p.code AS CODE, p.model, COALESCE(c.category_name, p.model) AS NAME 
FROM products p 
LEFT JOIN categories c 
ON p.category_id = c.category_id 
WHERE p.model LIKE "%LAPTOP%" OR c.category_name LIKE "%LAPTOP%"

tested in db-fiddle

CodePudding user response：

It seems to me what you really want is something like this:

SELECT *
 FROM categories c 
 LEFT JOIN products p 
 ON c.category_id = p.category_id
AND c.category_name LIKE "%LAPTOP%";

However, I can't help to think that since you have p.model LIKE "%LAPTOP%" in your condition, you're probably aiming for something a little bit different. Check out the result in this fiddle and tell me if this is close to what you're looking for or not:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4016c1e1a09c1d718f06a6605fd9677b

CodePudding user response：

@RobroyBustilloCanales

your query is correct. you have mentioned category_id in product table as text so you need to cast that to integer before join and use single quotes instead of double quotes.

            SELECT p.code AS CODE, p.model AS NAME 
            FROM products p 
            LEFT JOIN categories c 
            ON cast (p.category_id as INTEGER) = c.category_id
            WHERE p.model LIKE '%LAPTOP%' OR c.category_name LIKE '%LAPTOP%';