question about a query [ get all null and filled answers by question and category ]

[FALCONSEYE]

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?

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.

 

[johnherman]

Maybe you could use tbl_answers as your base table and outer join to the other tables.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[TamarGranor]

SELECT tbl_Questions.Description, 
       tbl_Answers.AnswerText, 
       tbl_category.Description,
       tbl_users.FirstName, 
       tbl_users.LastName 
  FROM tbl_Questions 
    JOIN tbl_Category 
      ON tbl_Questions.CategoryID = tbl_Category.CategoryID
    LEFT JOIN tbl_Answers
      ON tbl_Questions.QuestionID = tbl_Answers.QuestionID 
    LEFT JOIN tbl_Users
      ON tbl_Answers.UserID = tbl_Users.UserID

BTW, you have three of the tables named in the plural and one in the singular. It's good to make a rule about stuff like that and always do it the same way.

Tamar

 

[imex]

Hi,

Try:

select
    u.userID,
    u.firstName,
    u.lastName,
    q.questionID,
    q.description,
    c.categoryID,
    c.description,
    a.answer_id,
    a.answerText    
from tbl_users as u
cross join tbl_questions as q
inner join tbl_category as c
    on c.categoryID = q.categoryID
left join tbl_answers as a
    on a.userID = u.userID and
       a.questionID = q.questionID 
order by
    u.userID,
    q.questionID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[FALCONSEYE]

Thanks. This does the trick.

SELECT  
    u.*, c.*, q.*, a.*     
FROM
    tbl_category AS c
   JOIN 
    tbl_questions AS q  ON  q.categoryID = c.categoryID
  CROSS JOIN
    tbl_users AS u 
  LEFT JOIN
    tbl_answers AS a  ON  a.questionID = q.questionID
                      AND a.userID = u.userID
ORDER BY
    u.userID ;

ColdFusion Ninja for hire.