October 13th, 2004, 11:01 AM
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 12
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
SQL: Quering the results of a subquery.
Hi Folks,
Hope someone can help with this, I'm a newbie to SQL. I have a list of movie names and where and when these movies are showing. What I want to do is return a distinct list of movies by name. Users can then drill down for a full list of where and when that movie is showing.
Here's the code I'm currently using:
Code:
SELECT
movieGenre.mgKey,
movieLineUp.mluDateStart,
movieLineUp.mluEventStart,
movieEventName.menMovieEventName,
movieLineUp.mluChanName,
movieLineUp.mluPremier,
movieGenre.mgGenreName,
movieLineUp.mluKey
FROM
movieLineUp
INNER JOIN
(movieEventName
INNER JOIN
movieGenre
ON
movieEventName.men_snKey = movieGenre.mgKey)
ON movieLineUp.mluMovieTitle = movieEventName.menRefMovieEventName
WHERE
(((movieLineUp.mluDateStart)>#10/12/2004#)
AND ((movieEventName.menMovieEventName) Like "%the%"))
ORDER BY
movieLineUp.mluDateStart,
movieLineUp.mluEventStart;
This code works ok, but returns multiple instances of the same movie, with the various start times.
What I'd like to do is use the above as a subquery, and then use SELECT FIRST(movieGenre.mgKey)AS...etc
to return the first entry for each movie name.
Problem is, I can't seem to put this into practice.
Hope someone can help,
G
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
