PHP MySQL Combining 2 queries

Question

Table genre

id | genre     | display
---+-----------+------------
1  | Action    | 1
2  | Comedy    | 0
3  | Romance   | 1

Table movies

id | genre_id  | movie         | year    | display
---+-----------+---------------+---------+------------
1  | 1         | Kill Bill     | 2003    | 1
2  | 1         | Die Hard      | 1988    | 0
3  | 2         | Anchorman     | 2004    | 1
4  | 3         | Titanic       | 1997    | 1
5  | 3         | Casablanca    | 1942    | 1

Query

SELECT genre.genre, movies.movie, movies.year FROM `genre` JOIN `movies` ON
genre.id = movies.genre_id WHERE genre.display = 1 AND movies.display = 1

I'm trying to write a sql statement so that I get the result below in this specific order. If a genre has a display of 1, then the genre itself will be displayed, as well as any of its movies that have a display of 1. Also, genre names shouldn't be duplicated:

Action
- Kill Bill
- 2003
Romance
- Titanic
- 1997
- Casablanca
- 1942

Gordon Linoff · Accepted Answer · 2013-05-31 02:30:40Z

2

Your query selects the right rows. I think the only issue is the ordering:

SELECT genre.genre, movies.movie, movies.year
FROM `genre` JOIN
     `movies`
     ON genre.id = movies.genre_id
WHERE genre.display = 1 AND movies.display = 1
order by genre.genre, movies.year desc;

If you don't want the genre to duplicate, you can use a funky trick. That is, union the genres in with NULL movies, then order them correctly and remember to remove the names after that:

select (case when movie is NULL then genre else NULL end) as genre,
       movie, year
from ((select genre, NULL as movie, NULL as year
       from genre
       where display = 1
      ) union all
      (SELECT genre.genre, movies.movie, movies.year
       FROM `genre` JOIN
            `movies`
            ON genre.id = movies.genre_id
       WHERE genre.display = 1 AND movies.display = 1
      )
     ) t
order by genre,
         movie is null desc,
         year desc

Normally, I would say to do this in the app layer, but it can be fun to put together the SQL for something like this.

edited May 31, 2013 at 2:30

answered May 31, 2013 at 1:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

raina77ow Over a year ago

What if genre.display check will move into JOIN clause?

Barmar Over a year ago

@raina77ow It shouldn't make a difference.

Gordon Linoff Over a year ago

@raina77ow . . . It doesn't make a difference for an inner join.

Orangepill Over a year ago

its not apparent based on the output what the second order should be

user1307016 Over a year ago

@Orangepill the order should display the genre name once and go through any movies and its related years that it has. Also, there is a condition. Only those genres and its subsequent movies that have a display of 1 will be outputted.

|

Collectives™ on Stack Overflow

PHP MySQL Combining 2 queries

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related