|
Subject:
|
sql statement for asp.net page
|
|
Posted By:
|
carswelljr
|
Post Date:
|
8/24/2006 9:05:20 AM
|
Good morning,
I have a database (Tests) that has the following fields:
Doc_ID (Text)
Doc_Title (Text)
Test1 (yes/no)
Test2 (yes/no)
Test3 (yes/no)
Test4 (yes/no)
Test5 (yes/no)
Here is my dilema, I do not want to put the same Doc_ID (eg. doc_123) for 3 different tests to be run. (eg. Doc_123 needs Test 1, Test 3, and Test 5 to be run) Is there a SQL statement or maybe a script I can write to call to the Database for that one record to display the results 3 times on my webpage? (Instead of me creating a new Doc_Id for the same record 3 times in the Database) Does this make any sense?
thank you for your help.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
8/26/2006 11:44:55 AM
|
Based on your datatypes I would have to say that you are using ACCESS rather than SQL Server.
Next, if I understand your schema correctly you are violating 1st Normal Form and that is causing your difficulties.
I would look at normalizing your database design.
In the meantime this might work
SELECT Doc_ID, Doc_Title, 'Test1'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test1 = 'y' -- or true or whatever is in access
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test2'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test2 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test3'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test3 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test4'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test4 = 'y'
UNION ALL
SELECT Doc_ID, Doc_Title, 'Test5'
FROM Docs -- or whatever your table is named
WHERE DOC_Id = 'Doc_123'
AND Test5 = 'y'
IF You normalize your design then you could do something like this
SELECT d.Doc_ID, DOc_Title, TT.TestTypeDesc
FROM Docs D
JOIN DocTests DT
ON D.Doc_ID = DT.Doc_ID
JOIN TestTypes TT
ON DT.TestTypeID = TT.TestTypeID
WHERE D.Doc_ID = 'Doc_123' -- although you will be better off if you switch that to an integer identity (in access autonumber)
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
carswelljr
|
Reply Date:
|
8/28/2006 1:05:32 PM
|
Thank you. You are correct, presently the database i am using is Access 2003, but i am going to migrate to sql server 2005 this week. This should help me out alot
|
