[Kode]

I have narrowed it down a bit I think:

incomplete:

SELECT movie_name, IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '10' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS movielogo,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '11' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS movieart,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '12' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS moviedisc,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '16' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS moviebackground,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '19' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS moviebanner,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '23' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS hdmovielogo,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '24' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS moviethumb,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '26' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS hdmovieclearart,IF((SELECT COUNT(image_id) FROM movie_images WHERE image_active = 'y' AND image_type = '29' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS movieposter FROM movie_items HAVING movielogo > 0 OR movieart > 0 OR moviedisc > 0 OR moviebackground > 0 OR moviebanner > 0 OR hdmovielogo > 0 OR moviethumb > 0 OR hdmovieclearart > 0 OR movieposter > 0

This works, and the coulmns and having sections are built with php, it returns a 0 if there are images, and a 1 in there are none, the idea was to SUM these and order by that, therefore the rows with the lowest number would be the most complete, however, you can't SUM aliases and not sure how to rearrange the query to do this, an easier to read example is:

SELECT 
      movie_name, 
      IF((SELECT COUNT(image_id) AS images FROM movie_images WHERE image_active = 'y' AND image_type = '10' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS movielogo,
      IF((SELECT COUNT(image_id) AS images FROM movie_images WHERE image_active = 'y' AND image_type = '11' AND image_movie_tmdb_id = movie_tmdb_id) > 0, 0, 1) AS movieart 
      FROM movie_items 
      HAVING 
            movielogo > 0 
            OR 
            movieart > 0

this produces something like:

movie_name                                        movielogo	       movieart
Harry Potter and the Goblet of Fire                   0	                     1
Harry Potter and the Deathly Hallows: Part 1	      0	                     1
Blade	                                             0	                1

[Kode]

I suppose the other option is to do 2 queries, 1 to get all the possible sections and then use the results of that in php to build the second query

[Kode]

I want to do 2 queries, 1 gets a list of movies that are complete (every image section has at least 1 image), and 1 gets a list of movies that is incomplete ordered by the closest to completion.

so the first query has to do the basic following steps (not necessarily in this order)
* Get all the movies
* For each movie get all the possible image types
* For each image type count the amount of approved images for that movie that are also the same type
* If every image type count is over 1 output it

The second query needs to:
* Get all the movies
* For each movie get all the possible image types
* For each image type count the amount of approved images for that movie that are also the same type
* If not every image type count is over 1 output it ordered by sections incomplete.

tables used and relevant columns

table:fanart_sections (has all the sections, in this case we use section_id 3 which is movies, this isn't directly called because the section_id will be set, it's just so you know where the type_section will come from)
section_id
section_name

table: movie_items
movie_tmdb_id
movie_name

table: fanart_types (type_section will be 3)
type_id
type_name
type_section
type_active

table:movie_images (image type will be the type_id from the table above)
image_id
image_movie_tmdb_id
image_url
image_type

Any ideas?