Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 24th, 2006, 10:55 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Statement???

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.



  #2 (permalink)  
Old August 24th, 2006, 01:44 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Yes, there is way and it mostly has to do with the way you design the database. You could create separate tables for each type of data, one table for docs, one table for tests and an intermediary table to hold docs/tests relationships. You'll be able to have a scalable application and be able to add tests to the tests table without having to add another column to the docs table everytime there is a new test.


CREATE TABLE [Docs] (
    [DocID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Doc_Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Status] [bit] NOT NULL CONSTRAINT [DF_Docs_Status] DEFAULT (1),
    CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
    (
        [DocID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Tests] (
    [TestID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Test_Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Status] [bit] NOT NULL CONSTRAINT [DF_Tests_Status] DEFAULT (1),
    CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED
    (
        [TestID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [DocTests] (
    [DocID] [int] NOT NULL ,
    [TestID] [int] NOT NULL ,
    CONSTRAINT [FK_DocTests_Docs] FOREIGN KEY
    (
        [DocID]
    ) REFERENCES [Docs] (
        [DocID]
    ),
    CONSTRAINT [FK_DocTests_Tests] FOREIGN KEY
    (
        [TestID]
    ) REFERENCES [Tests] (
        [TestID]
    )
) ON [PRIMARY]
GO

Populate the Docs and Tests tables and then you can populate the Doctests table using the primary keys/ID column info. Then use the query below:


SELECT Docs.Doc_Title, Tests.Test_Title
FROM DocTests INNER JOIN
                      Docs ON DocTests.DocID = Docs.DocID INNER JOIN
                      Tests ON DocTests.TestID = Tests.TestID
--To look at all of the Docs and Tests or add the WHERE statement
WHERE (Docs.DocID = 1)





  #3 (permalink)  
Old August 25th, 2006, 12:40 PM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

perfecto!!! thanks for your help




Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with SQL Statement forumsUID SQL Server 2000 1 August 23rd, 2007 08:08 AM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
SQL Statement rylemer SQL Language 3 October 21st, 2005 09:07 PM
Sql Statement help morpheus Classic ASP Basics 0 March 9th, 2004 10:55 AM
sql statement chrisangus@btinternet.com Beginning VB 6 2 June 23rd, 2003 04:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.