SQL limit for LEFT JOINed table

Question

I have the following tables.

1. Industry(id, name)
2. Movie(id, name, industry_id) [Industry has many movies]
3. Trailer(id, name, movie_id) [Movie has many trailers]

I need to find 6 latest trailers for each Industry. Every movie does not need to have a trailer or can have multiple[0-n].

CREATE TABLE industry(id int, name char(10), PRIMARY KEY (id));

CREATE TABLE movie(id int, name char(10), industry_id int, PRIMARY KEY (id),
FOREIGN KEY (industry_id) REFERENCES industry(id));

CREATE TABLE trailer(id int, name char(10), movie_id int, PRIMARY KEY (id),
FOREIGN KEY (movie_id) REFERENCES movie(id));

INSERT INTO industry VALUES (1, "sandalwood");
INSERT INTO industry VALUES (2, "kollywood");

INSERT INTO movie VALUES (1, "lakshmi", 1);
INSERT INTO movie VALUES (2, "saarathi", 2);

INSERT INTO trailer VALUES (1, "lakshmi1", 1);
INSERT INTO trailer VALUES (2, "lakshmi2", 1);
INSERT INTO trailer VALUES (3, "lakshmi3", 1);
INSERT INTO trailer VALUES (4, "lakshmi4", 1);
INSERT INTO trailer VALUES (5, "lakshmi5", 1);
INSERT INTO trailer VALUES (6, "lakshmi6", 1);

INSERT INTO trailer VALUES (7, "saarathi4", 2);
INSERT INTO trailer VALUES (8, "saarathi5", 2);
INSERT INTO trailer VALUES (9, "saarathi6", 2);

SELECT  c.*
FROM    industry a
        LEFT JOIN movie b
            ON a.id = b.industry_id
        LEFT JOIN trailer c
            ON b.id = c.movie_id
LIMIT 0, 6

| ID |     NAME | MOVIE_ID |
----------------------------
|  1 | lakshmi1 |        1 |
|  2 | lakshmi2 |        1 |
|  3 | lakshmi3 |        1 |
|  4 | lakshmi4 |        1 |
|  5 | lakshmi5 |        1 |
|  6 | lakshmi6 |        1 |

I need to fetch only one recent trailer from each movie. But I am getting all trailers for each movie. Please suggest me to get the SQL statement.

Answer 1

I'm not sure if this works in MySql or not because I can't remember if you can have subqueries inside of an in clause, but you might try:

select * from trailer
where id in (select max(id) from trailer group by movie_id)

Whether it works or not, it looks like you're not using the industry table in your query so there's not much point in joining to it (unless you are actually trying to exclude movies that don't have any industry assigned to them... but based on your sample I it doesn't look like that was your intention).

If the above query doesn't work in MySql, then try this one

select  t.* 
from    trailer t join
        (select max(id) id from trailer group by movie_id) t2 on t1.id = t2.id

Answer 2

To get recent trailor you should include date field column from which we can fetch it