Require help to retrieve data from a table

Question

Require help to retrieve data from a table

Sumit Rastogi

SSC Eights!

Points: 838
More actions
July 5, 2013 at 7:06 am

#277085

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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]

Sumit Rastogi SSC Eights! Points: 838 More actions · Answer 3

Sumit Rastogi

SSC Eights!

Points: 838

July 5, 2013 at 9:33 pm

#1630210

Thanks SSCrazy, it helps me alot.

Sumit