I have the following tables in my bank:
I want to select all the products of a certain site (this site marked by the user through a checkbox ) with the following sql :
SELECT Eletrodomestico
FROM Produtos
WHERE URL LIKE (SELECT URL FROM site WHERE id = 1) || '%';
But only the first record of the bank, in the case liquidificador , is returned to me.
SELECT Electrodomestico
FROM Produtos p
INNER JOIN Sites s
ON locate(s.URL, s.URL) > 0
WHERE s.id = 1
This should return all records.
Edit: The way you want to do it, you can try:
SELECT Eletrodomestico
FROM Produtos
WHERE URL LIKE CONCAT((SELECT URL FROM sites WHERE id = 1), '%');
Your original instruction did not work because || does not concatenate two strings. This syntax is valid in DB2 but not in MySql. In MySql the | operator is the "Or". Translating your instruction to Portuguese results in:
Returns all the records for which
To understand better, and as a joke you can replace your original query with:
SELECT Eletrodomestico FROM Produtos WHERE URL LIKE (SELECT URL FROM site WHERE id = 1) || 1;
I leave only one opinion. It would be a lot easier if you could save in the Products table the Site ID. Facilitated and much, the join of the tables. It also influences performance.
SELECT Eletrodomestico
FROM Produtos, Sites
WHERE Sites.URL LIKE
CONCAT(Produtos.URL, '%') AND
Sites.id = 1;
Basically search for the url of the site within the url of the product, makes a junction between the records and prints what passes through the filter including site id.