Ailton Ailton - 1 month ago 10
MySQL Question

MySQL select inside select where text field is not empty

Please, how can I make this query work?

I already try D.descricao <> '' / D.descricao IS NOT NULL / ORDER BY LENGTH(descricao) DESC LIMIT 1 and nothing.

The Join of tables produtos and cluster8 return a number X of rows. In this rows some has the column descricao with some content and others are empty. With the subquery (line 2) I'm trying to get a record where descricao is not empty, but not sucessful until now.

And the reason I used the subquery is because when all rows has descricao = empty, even so will return the other fields in the main SELECT.

Here's the SQLfiddle: sqlfiddle.com/#!9/2806a/1

SELECT P.loja_id,
P.categoria_id,
C.centroide_produto_id,
(SELECT D.descricao
FROM produtos D
WHERE D.produto_id = C.centroide_produto_id
AND D.descricao <> ''
ORDER BY Length(descricao) DESC) AS descricao
FROM produtos P,
cluster8 C
WHERE P.produto_id = C.centroide_produto_id
AND C.centroide_produto_id = 1926
GROUP BY centroide_produto_id

Answer

If you are going to group by, then you need to use aggregate functions, such as sum, min, max... on all the fields which you select that aren't in the group by list. Also, I would suggest using an inner join instead of your 2nd select. Then you will only need a single where clause. You might need a having clause depending on your intentions and on which aggregate functions you choose.

Edit:

The following will not produce matching produto_id, nome and descricao, but it looks like that is what you wanted, based on your sqlfiddle. Also, it seems like you should be able to replace the whole ORDER BY clause with a max() on D.descricao in the select list, but you can tweak those things.

SELECT P.produto_id, 
       P.nome, 
       D.descricao
FROM   produtos P, 
       cluster8 C,
       produtos D
WHERE  P.produto_id = C.centroide_produto_id 
       AND C.centroide_produto_id = 10 
       AND D.produto_id = C.similar_produto_id 
       AND D.descricao <> ''
GROUP  BY centroide_produto_id, P.nome -- <-- added P.nome here
ORDER  BY Length(D.descricao) DESC