I have a complex query, is there any way to make it simpler?

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
Report Content as Inappropriate

I have a complex query, is there any way to make it simpler?

This post has NOT been accepted by the mailing list yet.
This post was updated on .
I have these tables:

movie (id, title, plot, etc)
genre (id, name)
movie_genre (movie_id, genre_id)

I want to get something like this:

group_name | group_count | name
action | 3 | Matrix
action | 3 | Braveheart
action | 3 | Terminator
drama | 2 | Braveheart
drama | 2 | Titanic
null     | 2 | movie without genre 1
null     | 2 | movie without genre 2

Note that I need to list and count the movies that have no genre.

These is what I have:

SELECT g.id group_id, coalesce(g.name, "no genre") group_name, c.count group_count, m.*
    FROM (
        SELECT id, title, rating, poster_path, coalesce(g.genre_id, 'no') gid
            FROM movie
            LEFT JOIN movie_genre g ON movie.id = g.movie_id
            WHERE (title LIKE "%blue%")
            ORDER BY movie.added DESC
    ) m
        SELECT coalesce(genre_id, 'no') gid,count(*) count
            FROM movie
            LEFT JOIN movie_genre ON movie.id = movie_genre.movie_id
            WHERE (title LIKE "%blue%")
            GROUP BY gid
    ) c ON m.gid = c.gid
    LEFT JOIN (SELECT "g" t,* FROM genre) g ON g.id = c.gid
    ORDER BY t DESC,g.name
    LIMIT 1000;

Some details:
1) If I want to search movies with title containing "blue", I need to do it in both sub queries in order to have correct group counts.
2) coalesce(genre_id, 'no') is necessary to join null values in order to have a "no genre" group.
3) ORDER BY t DESC is needed for sending the "no genre" group to the end of the result.

Is there any way to simplify this?
Thanks in advance.