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 statement 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 into Portuguese results in:
Returns all records for which
- URL in Products table equals URL in Sites table
- or '%' is true. But this second condition evaluates to FALSE.
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 just one opinion. It would be much easier if you could save the Site ID in the Products table. It would make it much easier to join the tables. It also influences the performance.
The table
Produtos
has notid
?– Jorge B.
@Jorgeb. Yes, they’re self-improved, I thought they wouldn’t be relevant to the problem, would they? 'Cause I can put them here.
– Ricardo
Are because you’re doing
WHERE id = 1
and what happens is that it assumes theid
tableProdutos
– Jorge B.
@Ricardohenrique only a small note, this query is not scalable. If possible, I would advise you to redesign the database structure, or when these tables have many lines, the query will be very slow.
– Miguel Mesquita Alfaiate
Could you give me a suggestion? yes and it is very extensive my email is available in my profile
– Ricardo
@Ricardohenrique the improvement is easy. the product table should have 1 ID which should be a Foreign key for the site it corresponds to. never make joins and comparisons with text fields.
– Miguel Mesquita Alfaiate