Require help to retrieve data from a table

  • Hi All,

    I have two tables as described below:

    CREATE TABLE [dbo].[Batch](

    [BatchID] [int] IDENTITY(1,1) NOT NULL,

    [BatchName] [nvarchar](50) NULL,

    [CourseID] [int] NULL,

    [DateFrom] [datetime] NULL,

    [DateTo] [datetime] NULL,

    CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED

    (

    [BatchID] 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

    SET IDENTITY_INSERT [dbo].[Batch] ON

    INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (1, N'B1', 1, CAST(0x0000A1F200000000 AS DateTime), CAST(0x0000A1FC00000000 AS DateTime))

    INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (2, N'B2', 2, CAST(0x0000A1F300000000 AS DateTime), CAST(0x0000A1FD00000000 AS DateTime))

    INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (3, N'B3', 3, CAST(0x0000A1F400000000 AS DateTime), CAST(0x0000A1FE00000000 AS DateTime))

    INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (4, N'B4', 4, CAST(0x0000A1F500000000 AS DateTime), CAST(0x0000A1FF00000000 AS DateTime))

    INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (5, N'B5', 5, CAST(0x0000A1F600000000 AS DateTime), CAST(0x0000A20000000000 AS DateTime))

    SET IDENTITY_INSERT [dbo].[Batch] OFF

    ------------

    CREATE TABLE [dbo].[Course_Module](

    [CourseModuleID] [int] IDENTITY(1,1) NOT NULL,

    [CourseID] [int] NOT NULL,

    [ModuleID] [int] NOT NULL,

    CONSTRAINT [PK_Course_Module] PRIMARY KEY CLUSTERED

    (

    [CourseModuleID] 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

    SET IDENTITY_INSERT [dbo].[Course_Module] ON

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (1, 1, 1)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (2, 1, 2)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (3, 1, 3)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (4, 1, 4)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (5, 2, 2)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (6, 2, 5)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (7, 2, 6)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (8, 2, 7)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (9, 3, 1)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (10, 3, 4)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (11, 3, 5)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (12, 4, 2)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (13, 4, 3)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (14, 5, 6)

    INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (15, 5, 7)

    SET IDENTITY_INSERT [dbo].[Course_Module] OFF

    Here I want a TSQL query which return those batches

    When I provide a CourseID then I should get all those batches which explicitly contains all the MODULEID attached with the provided CourseID.

    For Example:

    I have provided the CourseID = 4, then it should return CourseID 1 and 4 as both of them contains the ModuleID 2,3 but it should not return 1,2,3,4 as CourseID = 2 do not contain the ModuleID = 3 and CourseID = 3 do not contain the ModuleID = 2

    I have written the Code :

    Select distinct B.* from Batch B

    inner join course_Module CM on B.CourseID = CM.CourseID

    inner join Course_Module CMM on CM.ModuleID = CMM.ModuleID

    where CMM.CourseID = 4

    The Answer I am getting is :

    BatchNameCourseID

    B11

    B22

    B44

    Answer should be :

    BatchNameCourseID

    B11

    B44

  • I haven't got anywhere to test this at the moment, but I think you need to join Course_Module twice to Batch instead of once to Batch and once to itself.

    John

  • try this:

    ;WITH cteModules

    AS

    (

    SELECT ModuleId

    FROM Course_Module

    WHERE CourseID = 4

    )

    , cteQualifyingCources

    AS

    (

    SELECT cmc.CourseID

    FROM Course_Module AS cmc

    JOIN cteModules AS cm ON cm.ModuleID = cmc.ModuleID

    GROUP BY cmc.CourseID

    HAVING COUNT(*) = (SELECT COUNT(*) FROM cteModules)

    )

    SELECT *

    FROM Batch

    WHERE CourseID IN (SELECT CourseID FROM cteQualifyingCources)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks SSCrazy, it helps me alot.

    Sumit

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply