joining tables in view

I am now concerned that I have not normalised the tables correctly as now I see no way to join them;

I have 1 table with 2 columns called Questions, and another table called answers with 10 columns, one for the userId then 9 columns which hold all the answers (int) for each user.

Everything is working well for inserts and updates, however I am having a heck of a time trying to create a view which will show all questions, each user and each of their responses to each question.

Question table;

CREATE TABLE [dbo].[Questions](
[questionId]开发者_JAVA百科 [int] IDENTITY(1,1) NOT NULL,
[question] [nvarchar](max) NULL,
CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED 
(
[questionId] ASC
 )WITH (
        PAD_INDEX  = OFF, 
        STATISTICS_NORECOMPUTE  = OFF, 
        IGNORE_DUP_KEY = OFF,       
        ALLOW_ROW_LOCKS  = ON, 
        ALLOW_PAGE_LOCKS  = ON
       ) ON [PRIMARY]
 ) ON [PRIMARY]

Answer table;

CREATE TABLE [dbo].[Answers](
[empID] [nvarchar](10) NOT NULL,
[q1] [int] NULL,
[q2] [int] NULL,
[q3] [int] NULL,
[q4] [int] NULL,
[q5] [int] NULL,
[q6] [int] NULL,
[q7] [int] NULL,
[q8] [int] NULL,
[q9] [int] NULL,
 CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED 
(
[empID] ASC
)WITH (
       PAD_INDEX  = OFF, 
       STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF,   
       ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON
      ) ON [PRIMARY]
) ON [PRIMARY]

Nothing I have tried works, so any ideas from you all that can help me avoid re-doing the tables would be so greatly appreciated!

Thanks in advance for any help.

Alex

Seems to me that your answer table should have a foreign key to the question table. Unless an answer can refer to multiple questions, in which case I would say you should consider something like a mapping/relation table which has FKs to both answer and question tables.

I think you might need to look into pivot tables to accomplish your task.