FALCONSEYE
Programmer
Hello,
I have the following tables. I am trying to get a join of these 4 tables where it will list all the questions, answers even if the user didn't answer anything along with user and category.
any help?
ColdFusion Ninja for hire.
I have the following tables. I am trying to get a join of these 4 tables where it will list all the questions, answers even if the user didn't answer anything along with user and category.
any help?
Code:
CREATE TABLE [dbo].[tbl_users](
[userID] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](50) NULL,
[lastName] [varchar](50) NULL
)
GO
CREATE TABLE [dbo].[tbl_questions](
[questionID] [int] IDENTITY(1,1) NOT NULL,
[categoryID] [int] NOT NULL,
[description] [varchar](100) NULL
)
GO
CREATE TABLE [dbo].[tbl_category](
[categoryID] [int] IDENTITY(1,1) NOT NULL,
[description] [varchar](50) NULL
)
GO
CREATE TABLE [dbo].[tbl_answers](
[answer_id] [int] IDENTITY(1,1) NOT NULL,
[answerText] [varchar](250) NULL,
[questionID] [int] NULL,
[userID] [int] NULL
)
GO
-- tbl_questions
insert into tbl_questions ( categoryID, description )
values ( 1, 'How do you balance life and work?')
insert into tbl_questions ( categoryID, description )
values ( 1, 'Do you check voicemail and email when on vacation?')
insert into tbl_questions ( categoryID, description )
values ( 1, 'What is your favorite book?')
insert into tbl_questions ( categoryID, description )
values ( 2, 'What were your responsibilities?')
insert into tbl_questions ( categoryID, description )
values ( 2, 'What is your greatest strength?')
insert into tbl_questions ( categoryID, description )
values ( 2, 'What is your greatest weakness?')
insert into tbl_questions ( categoryID, description )
values ( 2, 'How do you evaluate success?')
-- tbl_users
insert into tbl_users ( firstName, lastName )
values ( 'Alessandra', 'Ambrosio' )
insert into tbl_users ( firstName, lastName )
values ( 'Adriana', 'Lima' )
insert into tbl_users ( firstName, lastName )
values ( 'Daniela', 'Pestova' )
-- tbl_answers
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q1', 1, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q2', 2, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q3', 3, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q4', 4, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q5', 5, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q6', 6, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'answer for q7', 7, 1)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'other answer for q5', 5, 2)
insert into tbl_answers ( answerText, questionID, userID )
values ( 'other answer for q2', 2, 2)
-- tbl_category
INSERT tbl_category (categoryID, description) VALUES (1, 'About You')
INSERT tbl_category (categoryID, description) VALUES (2, 'Job')
ColdFusion Ninja for hire.