SQL does not return all results that should return

Asked

Viewed 695 times

0

I have the following tables in my bank:

inserir a descrição da imagem aqui

and wish to select all products of a particular site (this site is 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 bank record is returned to me, in case liquidificador.

mysql sql

  • The table Produtos has not id?

  • @Jorgeb. Yes, they’re self-improved, I thought they wouldn’t be relevant to the problem, would they? 'Cause I can put them here.

  • Are because you’re doing WHERE id = 1 and what happens is that it assumes the id table Produtos

  • @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.

  • Could you give me a suggestion? yes and it is very extensive my email is available in my profile

  • @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.

Show 1 more comment

2 answers

2


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.

  • It worked but could explain me your SQL and what was wrong in my SQL?

  • I’ll edit it to try to explain why.

2

SELECT Eletrodomestico 
FROM Produtos, Sites
WHERE Sites.URL LIKE 
CONCAT(Produtos.URL, '%') AND  
Sites.id = 1;

Basically searches the site url within the product url, makes a merge between the records and prints what goes through the filter including site id.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.