CODEWITHSUNDEEP
mysqljoinphpmyadmin
Erik Figueiredo
Erik Figueiredo

Reputation: 325

Sort MySQL query with JOIN

I'm getting an error when I try to order my column on phpmyadmin result. This is the error:

enter image description here

My query is:

SELECT Cliente.Codigo, Cliente.Nome, Contato.Nome, Grupo.Nome FROM Cliente JOIN Grupo ON ( Grupo.codigo = Cliente.Codigo_Grupo ) JOIN Contato ON ( Contato.Codigo_Cliente = Cliente.Codigo ) GROUP BY Cliente.Codigo ASC

The ORDER BY was added automatically, when I click on title of column. Does anyone know how to solve this problem?

Upvotes: 0

Views: 84

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

I suspect this to be wrongly-gerated SQL: Try

SELECT Cliente.Codigo AS Codigo, Cliente.Nome, Contato.Nome AS Contato, Grupo.Nome AS Grupo 
FROM Cliente 
JOIN Grupo ON ( Grupo.codigo = Cliente.Codigo_Grupo ) 
JOIN Contato ON ( Contato.Codigo_Cliente = Cliente.Codigo ) 
GROUP BY Cliente.Codigo 
ORDER BY `Grupo` ASC
LIMIT 0,30

After discussion in the comments, it turns out, that an underlying bug in a framework is the culprit: - Without alias on Grupo.Nome it will choke on the Nome part - With alias on Grupo.Name it will choke on the alias

There is a really, really dirty way around this - but please be aware, that it is a workaround (at best), not a solution

Do this once:

CREATE VIEW GrupoWorkaround SELECT Grupo.*,Grupo.Nome AS Grupo FROM Grupo

This will create a parallel view.

No change your Query to 

SELECT Cliente.Codigo AS Codigo, Cliente.Nome, Contato.Nome AS Contato, GrupoWorkaround.Grupo
FROM Cliente 
JOIN GrupoWorkaround ON ( GrupoWorkaround.codigo = Cliente.Codigo_Grupo ) 
JOIN Contato ON ( Contato.Codigo_Cliente = Cliente.Codigo ) 
GROUP BY Cliente.Codigo 
ORDER BY `Grupo` ASC
LIMIT 0,30

in fact replaceing Grupo with GrupoWorkaround and thus obliverating the need for an alias.

Upvotes: 1

Related Questions