This is a seperate question than my other thread.I have tables:TracksTrackIDTrackNameArtistID (FK)GenreID (FK)StagesStageIDStageNameOccasionsOccasionIDOccasionNameTrackOccasionsIDTrackIDOccasionIDStageIDA track is associated with one or more occasions BUT ALSO can be for 1 or more stages for that occasionan occasion has one or more stagesim sure you can figure this out.My query gets tracks based on say, an artist name.The query is fine as I am doing inner joins. great. Problem: I only want to get a singular record for a track where the artist name matches for an occasion.Its difficult to explain. Maybe this will help:TrackID: 1Artist: Elton JohnTrackName: Candle In the WindOccasion: FirstOccasionStage: InitialTrackID: 1Artist: Elton JohnTrackName: Candle In the WindOccasion: FirstOccasionStage: MiddleTrackID: 1Artist: Elton JohnTrackName: Candle In the WindOccasion: FirstOccasionStage: Endso what I want is just to get the one record from the above search BUT the join for TrackOccasions must remain.how can I do this even if I have to abstract it out even more?SPROC:SELECT a.ArtistName, g.GenreName, g.GenreID, t.TrackID, t.TrackName, t.FullDuration, t.NonRegisteredPlaybackStartTime, t.NonRegisteredPlaybackEndTime, t.RegisteredPlaybackStartTime, t.RegisteredPlaybackEndTime, t.TrackDescription, t.TrackURL, t.GuidanceNotes, t.Religious, t.CatNo, t.Era, t.Tempo, t.Vocals, s.StageID, s.StageName, t.[Year], ROW_NUMBER() OVER (ORDER BY t.[TrackID] ASC) AS RowNumber FROM Tracks t INNER JOIN Artist a ON a.ArtistID = t.ArtistID INNER JOIN Genres g ON g.GenreID = t.GenreID INNER JOIN TrackOccasion tOcc ON tOcc.TrackID = t.TrackID INNER JOIN Stages s ON s.StageID = tocc.StageID WHERE a.ArtistName LIKE '%' + @keyword + '%'
single records with joins
Posted - 2011-07-07 : 17:35:18
