Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Wannabe67
Starting Member
14 Posts |
Posted - 2012-05-03 : 17:46:40
|
I need help writing a query that excludes the record below that I do not want. I want just records where StudentClassId = 1I have this query (which is not quite right):SELECT DISTINCT d1.DocumentTypeId, DocumentType, Title, (SELECT 1 FROM Documents WHERE (StudentClassId = @studentClassId AND DocumentTypeId = d1.DocumentTypeId)) AS 'Complete'FROM DocumentTypes d1LEFT JOIN Documents d2 ON d1.DocumentTypeId = d2.DocumentTypeIdWHERE d1.SchoolId = 4Here are my results with my query:DocumentTypeId DocumentType Complete Title1 Form1 1 SomeTitle by John2 Form2 NULL SomeTitle by John3 Form3 1 SomeTitle by John4 Form1 1 SomeTitle by Mark (I don’t want this record)CREATE TABLE [DocumentTypes]( [DocumentTypeId] [int] IDENTITY(1,1) NOT NULL, [DocumentType] [varchar](25) NOT NULL, [SchoolId] [int] NOT NULL, CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED ( [DocumentTypeId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [Documents]( [StudentDocumentId] [int] IDENTITY(1,1) NOT NULL, [DocumentTypeId] [int] NOT NULL, [StudentClassId] [int] NOT NULL, [SchoolId] [int] NOT NULL, [Document] [varbinary](max) NULL, [Title] [varchar](100) NOT NULL, CONSTRAINT [PK_Documents_1] PRIMARY KEY CLUSTERED ( [StudentDocumentId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Data in my DocumentTypes table looks like this:DocumentTypeId DocumentType SchoolId1 Form1 42 Form2 43 Form3 4 Completed Documents Table Data:StudentDocumentId DocumentTypeId StucentClassId SchoolId Document Title1 1 1 4 file.docx SomeTitle2 2 1 4 File.pdf SomeTitle3 1 2 4 File.doc SomeTitle4 3 1 4 File2.doc SomeTitle |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-03 : 19:31:30
|
your sample data and output doesnt make much sense. where did you get titles from? Also I cant even see Form1 in your sample data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
Wannabe67
Starting Member
14 Posts |
Posted - 2012-05-04 : 10:50:01
|
Please see revised topic above |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-04 : 18:46:12
|
why is schoolid not included in join? also you should be adding a where clause based on condition StudentClassId = 1 to filter only required columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|