How to make this query work?

Question

Hello i have the following query:

SELECT
    cmfilm.*,
    cmgenre.titel AS genretitel,
    cmvertoning.*,
    cmzaal.titel AS zaaltitel
FROM 
    cmfilm 
  LEFT JOIN cmvertoning ON cmvertoning.film_id=cmfilm.id 
  LEFT JOIN cmgenre ON cmfilm.genre_id=cmgenre.id
  LEFT JOIN cmzaal ON cmvertoning.zaal_id=cmzaal.id 
GROUP BY 
    cmfilm.titel 
SORT BY 
    cmgenre.titel ASC

this doesnt seem to work, there is a problem with the sorting part, anyone know where the problem is?

Answer 1

If your goal is to sort the result then shouldn't you be using ORDER BY?

Like this;

SELECT cmfilm.*, cmgenre.titel AS genretitel, cmvertoning.*, cmzaal.titel AS zaaltitel FROM cmfilm LEFT JOIN cmvertoning ON cmvertoning.film_id=cmfilm.id LEFT JOIN cmgenre ON cmfilm.genre_id=cmgenre.id LEFT JOIN cmzaal ON cmvertoning.zaal_id=cmzaal.id GROUP BY cmfilm.titel ORDER BY cmgenre.titel ASC

Answer 2

I think it should by ORDER BY in stead of SORT BY:

SELECT cmfilm.*, cmgenre.titel AS genretitel, cmvertoning.*, cmzaal.titel AS zaaltitel
FROM cmfilm
     LEFT JOIN cmvertoning ON cmvertoning.film_id=cmfilm.id
     LEFT JOIN cmgenre ON cmfilm.genre_id=cmgenre.id
     LEFT JOIN cmzaal ON cmvertoning.zaal_id=cmzaal.id
GROUP BY cmfilm.titel
ORDER BY cmgenre.titel:

If that is not the problem, please include the error message.

Answer 3

In SQL you need to group by ALL the non-aggregate columns you are selecting to get predictable results. Just because MySQL does not give you an error, doesn't make it right.

You seem to have cmfilm.titel in your group by clause, but you have no aggregate functions (sum/count etc.) , so as it is now, you probably should drop that group by , or tell us more about what you actually want to query.