Oracle 10g, how to query numerical value, (years) with specific limits on results
So the question I am posed with is to take the years produced of all of the movies in two genre's, (SH and CH) an then print out a list of all the movies, (title and year), that were produced before any of the movies in my specific genre were produced. I have this:
SELECT x.title "Title", x.yr "Year" FROM movies x WHERE EXISTS (SELECT x FROM movies y WHERE y.genre IN ('SH', 'CH') AND y.yr < x.yr) ORDER BY yr;
but it's producing all sorts of titles that were produced during and after the two genres had any of their movies produced. I would think that the less than would limit the results to anything under 1965, (the oldest move in either genre), but it doesn't, but if I use the greater than operator it does, (although it still pumps out newer results as well, so that doesn't work either)
Does anybody see what it is I am missing here? Thanks for any help.
Got it! I just needed to use the operator differently
WHERE yr < (SELECT insert stuff here); // this is homework so I can't post the full code
Seems I was just over complicating things.