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

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

Answer 2

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 3

If you want latest trailer by id of trailer table then use below query:

SELECT * FROM trailer t 
INNER JOIN (SELECT movie_id, MAX(id) id 
            FROM trailer GROUP BY movie_id) AS A ON t.id = A.id 

OR If you want data latest by date then use this query:

SELECT * FROM trailer t 
INNER JOIN (SELECT movie_id, MAX(latestbydate) latestbydate 
            FROM trailer GROUP BY movie_id
           ) AS A ON t.movie_id = A.movie_id  AND t.latestbydate = A.latestbydate

Answer 4

If you must do this all in SQL (and not in whatever backend or code you are using, which I would actually recommend) then you are probably going to have to rely on some variable magic.

Essentially, you need to "rank" each trailer by the date and then "partition" it by the movie that the trailer belongs to. These words have actual meaning in some other flavors of SQL (such as PL/SQL) but unfortunately don't have native functionality in MySQL.

You're going to want do to something similar to what is mentioned in this SO post. Once you get the "ranks" in there partitioned by movie_id, you just select WHERE rank < 6. The query could get pretty messy and there is some risk in using variables in that way but from what I can tell this is the best way to do it strictly with a MySQL query

Answer 5

Try this query

SELECT * FROM industry
LEFT JOIN movie on movie.industry_id = industry.id
LEFT JOIN (
  SELECT 
  id as T_ID,
  name as T_Name,
  movie_id
  FROM trailer 
  INNER JOIN ( SELECT 
              MAX(id) as TID
              FROM trailer
              GROUP BY movie_id
             ) as t on t.TID = trailer.id
) as c on c.movie_id = movie.id;

Here is the Demo

Answer 6

SELECT i.name, m.name, MAX(t.id) AS 'Latest Trailer ID', MAX(t.name) AS 'Latest Trailer'    
FROM industry i
INNER JOIN movie m ON(i.id = m.industry_id)
INNER JOIN trailer t ON(m.id = movie_id)
GROUP BY m.id