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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Selecting records from one table (Revised)

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 = 1

I 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 d1
LEFT JOIN Documents d2 ON d1.DocumentTypeId = d2.DocumentTypeId
WHERE d1.SchoolId = 4

Here are my results with my query:
DocumentTypeId DocumentType Complete Title
1 Form1 1 SomeTitle by John
2 Form2 NULL SomeTitle by John
3 Form3 1 SomeTitle by John
4 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 SchoolId
1 Form1 4
2 Form2 4
3 Form3 4


Completed Documents Table Data:
StudentDocumentId DocumentTypeId StucentClassId SchoolId Document Title
1 1 1 4 file.docx SomeTitle
2 2 1 4 File.pdf SomeTitle
3 1 2 4 File.doc SomeTitle
4 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

Wannabe67
Starting Member

14 Posts

Posted - 2012-05-04 : 10:50:01
Please see revised topic above
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -