This is for my studies, so I would prefer if you guys could give hints opposed to answers, I don't mind either though. :)
The following link is my data in a SQLFiddle: http://sqlfiddle.com/#!2/d7373
CREATE TABLE Movies
(`MovieID` int, `Title` varchar(6), `YearReleased` int)
;
INSERT INTO Movies
(`MovieID`, `Title`, `YearReleased`)
VALUES
(1002, 'movie1', 2001),
(1003, 'movie2', 1951),
(1004, 'movie3', 2001),
(1005, 'movie4', 2004),
(1006, 'movie2', 2007),
(1007, 'movie5', 2005),
(1008, 'movie2', 2010),
(1009, 'movie3', 2006),
(1010, 'movie6', 2003),
(1011, 'movie7', 2002),
(1012, 'movie8', 2004),
(1013, 'movie9', 2002)
;
What I want to do is to output the Name and Years Released of movies that have been released more than once. So if you look in the SQL Fiddle 'movie3' has been released twice, so i would like to output it's name and both years it was released. If a movie has been released 3 times it need to be displayed three times because there are 3 pairs release dates. The following is an example of how I would like the data in the SQL Fiddle to be outputted.
Movie | FirstRelease | SecondReleased
---------------------------------------
movie2 | 1951 | 2007
movie2 | 1951 | 2010
movie2 | 2007 | 2010
movie3 | 2001 | 2007
Thanks Guys.
If you want all pairs of release dates, you can do a simple self-join (fiddle):
SELECT m1.Title AS Movie, m1.YearReleased AS FirstRelease,
m2.YearReleased AS SecondReleased
FROM Movies m1
INNER JOIN Movies m2 ON m2.Title = m1.Title
AND m2.YearReleased > m1.YearReleased
This will correlate rows in Movies with rows having the same title and a greater release year, giving you all pairs.
If you only want release dates and the next subsequent release year as per the question before the edit, you could do a self-join, with a MIN (fiddle):
SELECT m1.Title AS Movie, m1.YearReleased AS FirstRelease,
MIN(m2.YearReleased) AS SecondReleased
FROM Movies m1
INNER JOIN Movies m2 ON m2.Title = m1.Title
AND m2.YearReleased > m1.YearReleased
GROUP BY m1.Title, m1.YearReleased
What this does is for each movie and each release year, it gets the MINimum (next) release year for the same movie. The INNER JOIN makes sure it only returns rows where there is a next release.
Note that this follows the request in your question, in that it will return multiple rows for each movie where there are many releases (e.g. movie2), where the column names "FirstRelease" and "SecondReleased" are a bit of a misnomer.
Apologies for giving the answer straight out, but I think the query speaks a thousand words.