Outputting Multiple Columns from one Table SQL?

Question

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.

I've added your schema from the fiddle here in the case sqlfiddle dies in the future, so this question can still be useful. — lc., Aug 15 '13 at 03:25
DO NOT vandalise your questions like this. If you have a new problem ask a new question. — ChrisF, Sep 12 '13 at 08:00

score 1 · Answer 1 · answered Aug 15 '13 at 03:27

1

Here is a hint:

Group by: http://www.w3schools.com/sql/sql_groupby.asp

Having: http://www.w3schools.com/sql/sql_having.asp

They are used in conjunction

Answer (Spoiler?):

http://sqlfiddle.com/#!2/d7373/4/0

answered Aug 15 '13 at 03:27

Ed W

125
3
18

lc. · Accepted Answer · 2013-08-15T04:08:31.123

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.

Yes, you are joining the `m1` instance of the table to `m2` instance of the same table. This is what is known as a self-join - correlating the Movies table to itself. Each instance can return different rows, which is how you are able to get `m1` to be all rows and `m2` to be only those rows with the same movie title as `m1` and a greater release year than `m1`. — lc., Aug 15 '13 at 03:49
By "The INNER JOIN makes sure it only returns rows where there is a next release.", I meant that it will only return rows where there is a corresponding row in `m2`. That is, it will not return rows like (`movie1`, `2001`, `NULL`) or (`movie3`, `2007`, `NULL`). Contrast this to using a `LEFT OUTER JOIN`. — lc., Aug 15 '13 at 03:51
@user1330649 This is actually the simpler version. In this case, just remove the `MIN` and the entire `GROUP BY` clause, and you will get all pairs. The `MIN`/`GROUP BY` made sure you got only the *next* release for each (title, release year) pair, and thus eliminating the (`movie2`, `1951`, `2010`) row. — lc., Aug 15 '13 at 04:06

Outputting Multiple Columns from one Table SQL?

2 Answers2