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 2000 Forums
 Transact-SQL (2000)
 re-structure sql statement and tables

Author  Topic 

-Dman100-
Posting Yak Master

210 Posts
Posted - 2006-06-09 : 18:35:31
I've recently been given some new applications to support and I'm trying to clean-up and normalize the structures.

One of the current structures I'm working on is a teacher evaluation form with a series of questions that are inserted into one of the tables. The tables to hold the data that I query from were built as follows:

CREATE TABLE [dbo].[Answers] (
[id] [int] NOT NULL ,
[Label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--the above table holds all the possible answers that can be choosen


CREATE TABLE [dbo].[Teacher_Survey] (
[id] [int] NOT NULL ,
[Teacher] [int] NOT NULL ,
[Q3] [int] NULL ,
[Q4A_math] [int] NOT NULL ,
[Q4B_math] [int] NOT NULL ,
[Q4A_rla] [int] NOT NULL ,
[Q4B_rla] [int] NOT NULL ,
[Q5] [int] NULL ,
[Q6] [int] NULL ,
[Q7_mo] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Q7_yr] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Q8] [int] NULL ,
[Q9] [int] NULL ,
[Q10A] [int] NOT NULL ,
[Q10B] [int] NOT NULL ,
[Q10C] [int] NOT NULL ,
[Q10D] [int] NOT NULL ,
[Q11A] [int] NOT NULL ,
[Q11B] [int] NOT NULL ,
[Q11C] [int] NOT NULL ,
[Q11D] [int] NOT NULL ,
[Q12A] [int] NOT NULL ,
[Q12B] [int] NOT NULL ,
[Q12C] [int] NOT NULL ,
[Q12D] [int] NOT NULL ,
[Q12E] [int] NOT NULL ,
[Q12F] [int] NOT NULL ,
[Q13] [int] NULL ,
[Q14] [int] NULL ,
[Q15] [int] NULL ,
[Q16] [int] NULL ,
[Q17_classA] [int] NOT NULL ,
[Q17_classB] [int] NOT NULL ,
[Q17_classC] [int] NOT NULL ,
[Q17_classD] [int] NOT NULL ,
[Q17_labA] [int] NOT NULL ,
[Q17_labB] [int] NOT NULL ,
[Q17_labC] [int] NOT NULL ,
[Q17_labD] [int] NOT NULL ,
[Q18A] [int] NOT NULL ,
[Q18B] [int] NOT NULL ,
[Q18C] [int] NOT NULL ,
[Q18D] [int] NOT NULL ,
[Q18E] [int] NOT NULL ,
[Q18F] [int] NOT NULL ,
[Q18G] [int] NOT NULL ,
[Q18H] [int] NOT NULL ,
[Q19A] [int] NOT NULL ,
[Q19B] [int] NOT NULL ,
[Q19C] [int] NOT NULL ,
[Q19D] [int] NOT NULL ,
[Q19E] [int] NOT NULL ,
[Q19F] [int] NOT NULL ,
[Q19G] [int] NOT NULL ,
[Q19H] [int] NOT NULL ,
[Q19I] [int] NOT NULL ,
[Q19J] [int] NOT NULL ,
[Q19K] [int] NOT NULL ,
[Q20] [int] NULL ,
[Q21] [int] NULL ,
[Q22] [int] NULL ,
[Q23] [int] NULL ,
[Q24] [int] NOT NULL ,
[Q25] [int] NOT NULL ,
[Submit_Date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

This is the following select statement I used the return results of the evaluations:

SELECT t.LName AS LastName,t.Grade,t.LabNo,
q3,
q4a_math,
q4b_math,
q4a_rla,
q4b_rla,
q5.label AS q5,
q6.label AS q6,
q7_mo,
q7_yr,
q8.label AS q8,
q9,
q10a.label as q10a,
q10b.label as q10b,
q10c.label as q10c,
q10d.label as q10d,
q11a.label as q11a,
q11b.label as q11b,
q11c.label as q11c,
q11d.label as q11d,
q12a.label as q12a,
q12b.label as q12b,
q12c.label as q12c,
q12d.label as q12d,
q12e.label as q12e,
q12f.label as q12f,
q13.label as q13,
q14.label as q14,
q15.label as q15,
q16.label as q16,
q17_classa.label as q17_classa,
q17_classb.label as q17_classb,
q17_classc.label as q17_classc,
q17_classd.label as q17_classd,
q17_laba.label as q17_laba,
q17_labb.label as q17_labb,
q17_labc.label as q17_labc,
q17_labd.label as q17_labd,
q18a.label as q18a,
q18b.label as q18b,
q18c.label as q18c,
q18d.label as q18d,
q18e.label as q18e,
q18f.label as q18f,
q18g.label as q18g,
q18h.label as q18h,
q19a.label as q19a,
q19b.label as q19b,
q19c.label as q19c,
q19d.label as q19d,
q19e.label as q19e,
q19f.label as q19f,
q19g.label as q19g,
q19h.label as q19h,
q19i.label as q19i,
q19j.label as q19j,
q19k.label as q19k,
q20.label as q20,
q21.label as q21,
q22.label as q22,
q23.label as q23,
q24,
q25,
Submit_Date
FROM dbo.Teacher_Survey_102005 ts
INNER JOIN Teachers t ON ts.Teacher = t.id
INNER JOIN Answers q5 ON q5.id = ts.q5
INNER JOIN Answers q6 ON q6.id = ts.q6
INNER JOIN Answers q8 ON q8.id = ts.q8
INNER JOIN Answers q10a ON q10a.id = ts.q10a
INNER JOIN Answers q10b ON q10b.id = ts.q10b
INNER JOIN Answers q10c ON q10c.id = ts.q10c
INNER JOIN Answers q10d ON q10d.id = ts.q10d
INNER JOIN Answers q11a ON q11a.id = ts.q11a
INNER JOIN Answers q11b ON q11b.id = ts.q11b
INNER JOIN Answers q11c ON q11c.id = ts.q11c
INNER JOIN Answers q11d ON q11d.id = ts.q11d
INNER JOIN Answers q12a ON q12a.id = ts.q12a
INNER JOIN Answers q12b ON q12b.id = ts.q12b
INNER JOIN Answers q12c ON q12c.id = ts.q12c
INNER JOIN Answers q12d ON q12d.id = ts.q12d
INNER JOIN Answers q12e ON q12e.id = ts.q12e
INNER JOIN Answers q12f ON q12f.id = ts.q12f
INNER JOIN Answers q13 ON q13.id = ts.q13
INNER JOIN Answers q14 ON q14.id = ts.q14
INNER JOIN Answers q15 ON q15.id = ts.q15
INNER JOIN Answers q16 ON q16.id = ts.q16
INNER JOIN Answers q17_classa ON q17_classa.id = ts.q17_classa
INNER JOIN Answers q17_classb ON q17_classb.id = ts.q17_classb
INNER JOIN Answers q17_classc ON q17_classc.id = ts.q17_classc
INNER JOIN Answers q17_classd ON q17_classd.id = ts.q17_classd
INNER JOIN Answers q17_laba ON q17_laba.id = ts.q17_laba
INNER JOIN Answers q17_labb ON q17_labb.id = ts.q17_labb
INNER JOIN Answers q17_labc ON q17_labc.id = ts.q17_labc
INNER JOIN Answers q17_labd ON q17_labd.id = ts.q17_labd
INNER JOIN Answers q18a ON q18a.id = ts.q18a
INNER JOIN Answers q18b ON q18b.id = ts.q18b
INNER JOIN Answers q18c ON q18c.id = ts.q18c
INNER JOIN Answers q18d ON q18d.id = ts.q18d
INNER JOIN Answers q18e ON q18e.id = ts.q18e
INNER JOIN Answers q18f ON q18f.id = ts.q18f
INNER JOIN Answers q18g ON q18g.id = ts.q18g
INNER JOIN Answers q18h ON q18h.id = ts.q18h
INNER JOIN Answers q19a ON q19a.id = ts.q19a
INNER JOIN Answers q19b ON q19b.id = ts.q19b
INNER JOIN Answers q19c ON q19c.id = ts.q19c
INNER JOIN Answers q19d ON q19d.id = ts.q19d
INNER JOIN Answers q19e ON q19e.id = ts.q19e
INNER JOIN Answers q19f ON q19f.id = ts.q19f
INNER JOIN Answers q19g ON q19g.id = ts.q19g
INNER JOIN Answers q19h ON q19h.id = ts.q19h
INNER JOIN Answers q19i ON q19i.id = ts.q19i
INNER JOIN Answers q19j ON q19j.id = ts.q19j
INNER JOIN Answers q19k ON q19k.id = ts.q19k
INNER JOIN Answers q20 ON q20.id = ts.q20
INNER JOIN Answers q21 ON q21.id = ts.q21
INNER JOIN Answers q22 ON q22.id = ts.q22
INNER JOIN Answers q23 ON q23.id = ts.q23
WHERE LabNo = '202728'

First off, is there a better way to structure the tables? Separate into a third table?

Second, is there a better way to query this information? This seems like a ridiculous select statement.

I want to re-write this in a more efficient manner.

If I can provide any additional information, please let me know.

Any help would be appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-06-10 : 03:38:07
Read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site