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)
|
