0

I have a filmdatabase with a lot of movies. A movie can have a previous part (or next part). I want to run a query that presents a list with all previous movies and all next movies. The table has the following columns: movie_id|title|previous_part(which is the movie id of the previous part)|publication_year

This is what I have now, but it won't work

SELECT TOP 1 e1.movie_id, e1.title, e1.previous_part
FROM dbo_Movie AS e1 INNER JOIN dbo_Movie AS e2 ON e1.movie_id = e2.previous_part
ORDER BY e1.publication_year;

e1.movie_id is a paramameter of one random part of a movie.

Thanks in advance!

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
Robert
  • 81
  • 7

1 Answers1

0

Can you clarify the following:

1) How many is a lot of movies 1000 /100,000 / 10,000,000 ( e.g personal collection, small application, global library) 2) How many parts are you looking at and how would you like the data presented?

Logically wise if there is a reasonable amount of finite parts ( less than 10) you could make some ungainly sql construct to list all previous parts and future parts. How ever if you're just after the single previous if it exists and next if it exists you could try:

Solution 1: (lists all movie parts and the prev and next parts if they exist)

select e1.movie_id, e1.title, pp.movie_id as prev_movie_id,  np.movie_id as next_movie_id
from dbo_Movie AS e1
left join dbo_Movie AS pp
    ON e1.previous_part = pp.movie_id
left join dbo_Movie AS np
    ON np.previous_part = e1.movie_id

Expected output:

movie_id | title | prev_movie_id | next_movie_id
m1p2      | zed   | m1p1          | m1p3
m1p3      | zed   | m1p2          | null
m1p1      | zed   | null          | m1p2
m2p1      | phi   | null          | null
m3p1      | quo   | null          | m3p2
m3p2      | quo   | m3p1          | null

Solution 2.1: Lists all movie parts with previous next parts

select e1.movie_id, e1.title, np.movie_id as next_movie_id
from dbo_Movie AS e1
inner join dbo_Movie AS np  
    ON np.previous_part = e1.movie_id
--inner join means anything rows where this isn't true will be removed

Expected output:

movie_id | title | next_movie_id
m1p2      | zed   | m1p3
m1p1      | zed   | m1p2
m3p1      | quo   | m3p2

Solution 2.2: Lists all movie parts with previous next parts

select e1.movie_id, e1.title, pp.movie_id as prev_movie_id
from dbo_Movie AS e1
inner join dbo_Movie AS pp  
    ON e1.previous_part = pp.movie_id

Expected output:

movie_id | title | prev_movie_id 
m1p2      | zed   | m1p1         
m1p3      | zed   | m1p2            
m3p2      | quo   | m3p1

Notes:

1) Nulls are not dealt with cleanly which both prev_movie_id and next_movie_id could be.

2) There is apparently no ANSI sql standard for limiting results in SQL:1999 but there is in later standards as mentioned by bobince in reply to: Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

Community
  • 1
  • 1
Julian Woodhouse
  • 184
  • 6
  • Thanks a lot for your answer. Unfortunately it doesn't work yet. I've about 400.000 movies in my DB. I've a button in Access. It opens this query and it has a parameter with the movie_id. For example: I choose to see al parts of fast and furious, when I press the button of fast and furious 4. Then I want a list to show ONLY the fast and the furious parts. So: - fast and furious 1 - fast and furious 2 -.... - fast and furious 6 – Robert Jan 07 '15 at 13:29
  • Okay, So is the use case that you would only ever be querying one movieid at a time? In addition can you provide the DDL for the table? Lastly I assume it's not working due to a poor performance - is there an error being raised or some other feedback? – Julian Woodhouse Jan 07 '15 at 18:59
  • It looks like you need to be able apply a filter on the the request for data from Access (I'm rusty on what can be done dynamically with requesting data from Access to MySQL). – Julian Woodhouse Jan 07 '15 at 19:06
  • Is there a particular reason to go with the Access front end and MySQL backend? it looks like it will restrict most simple smarts you would use with alternative implimentations. Useful doc here but no filtering (http://watchdoglabs.org/data/2013/NIMSP/ODBC-Access/NIMSP-MySQL-ODBC-AccessFrontEnd.pdf) I suggestion updating you Question description with additional back ground information. – Julian Woodhouse Jan 08 '15 at 11:42