Many to Many Table relationship

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

General SQL Server Forums

Database Design and Application Architecture

Many to Many Table relationship

Author

Topic

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 01:13:36

Am designing a table to hold data containing students Test that will be uploaded from csv file. I can upload data to Answer, Person and Question Table But cannt retrieve useful information..My table design is below;

CREATE TABLE [dbo].[Answer](
[AnswerID] [int] IDENTITY(1,1) NOT NULL,
[AnswerDescription] [ntext] NULL,
[StudentResponse] [ntext] NULL,
[PossiblePoint] [ntext] NULL,
[AutoScore] [ntext] NULL,
CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED
(
[AnswerID] ASC
CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[ModuleTaught] [nvarchar](50) NULL,
[Department] [nvarchar](50) NULL,
[Position] [nvarchar](50) NULL,
[RoleName] [nvarchar](50) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC

CREATE TABLE [dbo].[Question](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[QuestionDescription] [nvarchar](max) NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC

CREATE TABLE [dbo].[Test](
[QuestionID] [int] NOT NULL,
[AnswerID] [int] NOT NULL,
[ModuleID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY NONCLUSTERED
(
[QuestionID] ASC,
[AnswerID] ASC,
[PersonID] ASC,
[ModuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Person]
GO

ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Question] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Question] ([QuestionID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Question]
GO

//When i run the below query i get nothing//
SELECT UserName, QuestionDescription, AnswerDescription
FROM Person
INNER JOIN Test
ON Person.PersonID = Test.PersonID
INNER JOIN Question
ON Question.QuestionID = Test.QuestionID
INNER JOIN Answer
ON Answer.AnswerID = Test.AnswerID
Where AnswerID = '1'

PLEASE HELP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:34:13

the query looks fine. may be you dont have any data for the value answerid=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 01:59:34

I got data Data in Answer, Question and Person table but none in test table...Thinking Test table will automatically populate when i insert data in other tables as it stands as the junction table ....
Pls tell if i need to populate TEST table manually and how?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:06:17

yup. you need to populate test table manually. what all values you get from users?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 02:40:07

I got a csv fie that is in the below format;
Usernam,lastname,firstname,question,answer,score,stud_score
I created those tables to accomodate these colums and imported data into these columns from the flat file.
QuestionsID, AnswerID and PersonID are identity columns in these tables and are representing the tables in TEST table.i dnt know how to manually update the TEST

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:48:59

ok. are you using export import wizard or openrowset for exporting data for tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 02:56:52

Am using SSIS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:59:31

oh ok. for your scenario its better to use OPENROWSET to insert records programatically, then use SCOPE_IDENTITY to capture generated ID value and then populate the test table with it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 03:02:08

I will need to import from one flat file to multiple tables. will OPENROWSET give me the capability ...can u give me a guide pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 03:07:03

yup. by using OPENROWSET you can use only columns you want to populate onto table. see

http://www.databasejournal.com/features/mssql/article.php/3584751/OPENROWSET-function-in-SQL-Server-2005.htm

http://msdn.microsoft.com/en-us/library/ms190312.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ndidiamaka
Starting Member

9 Posts

Posted - 2010-08-03 : 03:20:06

I assume its gonna be difficult to use OPENROWSET, the examples are related to importing into a single table and i need to import into three tables and the fourth table updated automatically as a result of the import to other tables...Really need more ideas pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 03:34:47

it would be like

INSERT INTO Table1
SELECT reqd columns alone...
FROM OPENROWSET (...)

SET @ID1 = SCOPE_IDENTITY()

INSERT INTO Table2
SELECT reqd columns...
FROM OPENROWSET()
SET @ID2=SCOPE_IDENTITY
...

and at last

INSERT INTO Table4
VALUES(@ID1,@ID2,..)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources