A little difficult to explain but here goes.
CREATE TABLE [dbo].[SurveyCourse](
_ [ID] [int] IDENTITY(1,1) NOT NULL,_
_ [CourseID] [int] NOT NULL,_
_ [SurveyID] [int] NOT NULL,_
_ [MasterSurveyID] [int] NOT NULL,_
_ CONSTRAINT [PK_SurveyCourse] PRIMARY KEY CLUSTERED _
(
_ [ID] ASC_
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO SurveyCourse VALUES (2, 101, 3)
INSERT INTO SurveyCourse VALUES (2, 102, 3)
INSERT INTO SurveyCourse VALUES (2, 103, 3)
INSERT INTO SurveyCourse VALUES (2, 104, 3)
INSERT INTO SurveyCourse VALUES (5, 101, 3)
INSERT INTO SurveyCourse VALUES (5, 102, 3)
INSERT INTO SurveyCourse VALUES (5, 103, 3)
INSERT INTO SurveyCourse VALUES (5, 104, 3)
INSERT INTO SurveyCourse VALUES (8, 101, 4)
INSERT INTO SurveyCourse VALUES (8, 102, 4)
INSERT INTO SurveyCourse VALUES (8, 103, 4)
INSERT INTO SurveyCourse VALUES (8, 104, 4)
What I want is a query where, for a given MasterSurveyID, I want to retrieve just the first group of data (i.e only the CourseID where the MasterSurveyID is 3 but instead of giving me all records where Course ID is 2 and 5, JUST the first group which is where courseID is 5)
How can I achieve this?
using SQL Server 2008R2