SQL表加入查询(SQL table joins query)

SQL表加入查询(SQL table joins query)

我正在尝试计算问卷系统的答案。 我想在一个表中显示结果（问题，选项，响应数）。 我编写了一个可以正常工作的查询，但它没有显示所有选项，如果没有响应。

我的查询

SELECT R.QuestionID, Q.QuestionName, A.OptionName, COUNT(R.OptionID) AS Responses, A.OptionID FROM Response AS R INNER JOIN Question AS Q ON Q.QuestionID = R.QuestionID INNER JOIN Option AS A ON R.OptionID = A.OptionID WHERE (R.QuestionnaireID = 122) GROUP BY R.QuestionID, Q.QuestionName, A.OptionName, R.OptionID, A.OptionID

数据库结构 ：

问卷（问卷调查ID PK，问卷名称） 问题（questionID PK，问卷ID FK，问卷名称） 选项（OptionID PK，questionID FK，optionName） 响应（ResponseID PK，问卷ID FK，questionID FK，值）

表定义

CREATE TABLE [dbo].[Questionnaire] ( [QuestionnaireID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireName] NVARCHAR (100) NOT NULL, PRIMARY KEY CLUSTERED ([QuestionnaireID] ASC), ); CREATE TABLE [dbo].[Question] ( [QuestionID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireID] INT NOT NULL, [QuestionName] NVARCHAR (250) NOT NULL, PRIMARY KEY CLUSTERED ([QuestionID] ASC), CONSTRAINT [FK_Question_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID]) ); CREATE TABLE [dbo].[Option] ( [OptionID] INT IDENTITY (1, 1) NOT NULL, [QuestionID] INT NOT NULL, [OptionName] NVARCHAR (150) NOT NULL, PRIMARY KEY CLUSTERED ([OptionID] ASC), CONSTRAINT [FK_Option_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID]) ); CREATE TABLE [dbo].[Response] ( [ResponseID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireID] INT NOT NULL, [QuestionID] INT NOT NULL, [Val] NVARCHAR (150) NOT NULL, [OptionID] INT NULL, PRIMARY KEY CLUSTERED ([ResponseID] ASC), CONSTRAINT [FK_Response_Option] FOREIGN KEY ([OptionID]) REFERENCES [dbo].[Option] ([OptionID]), CONSTRAINT [FK_Response_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID]), CONSTRAINT [FK_Response_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID]) );

目前的数据：

insert into questionnaire values ('ASP.NET questionnaire'); insert into questionnaire values('TEST questionnaire'); insert into question values (2, 'rate our services'); insert into question values (2, 'On scale from 1 to 5, how much youre sleepy?'); insert into question values (2, 'how are you today'); insert into [Option] values (1, 'good'); insert into [Option] values (1, 'bad'); insert into [Option] values (1, 'medium'); insert into [Option] values(2, '1'); insert into [Option] values(2, '2'); insert into [Option] values(2, '3'); insert into [Option] values(2, '4'); insert into [Option] values(2, '5'); insert into [option] values (3, 'fine'); insert into [option] values (3, 'great'); insert into [option] values (3, 'not bad'); insert into [option] values (3, 'bad'); insert into response values(2, 1, 'good', 1); insert into response values(2, 1, 'good', 1); insert into response values(2, 1, 'bad', 2); insert into response values(2, 1, 'good', 1); insert into response values(2, 2, '1', 4); insert into response values(2, 2, '3', 3); insert into response values(2, 2, '4', 5); insert into response values(2, 2, '5', 8);

期望的输出

SQL小提琴

Sql小提琴

I'm trying to calculate responses for a questionnaire system. I want to show the result in one table (the question, options, number of responses). I wrote a query which works just fine however, it doesn't display all the options and if there are no responses for them.

My query

SELECT R.QuestionID, Q.QuestionName, A.OptionName, COUNT(R.OptionID) AS Responses, A.OptionID FROM Response AS R INNER JOIN Question AS Q ON Q.QuestionID = R.QuestionID INNER JOIN Option AS A ON R.OptionID = A.OptionID WHERE (R.QuestionnaireID = 122) GROUP BY R.QuestionID, Q.QuestionName, A.OptionName, R.OptionID, A.OptionID

database structure:

Questionnaire (questionnaireID PK, questionnaireName) Question (questionID PK, questionnaireID FK, questionnaireName) Option (OptionID PK, questionID FK, optionName) Response (ResponseID PK, questionnaireID FK, questionID FK, value)

Table definitions

CREATE TABLE [dbo].[Questionnaire] ( [QuestionnaireID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireName] NVARCHAR (100) NOT NULL, PRIMARY KEY CLUSTERED ([QuestionnaireID] ASC), ); CREATE TABLE [dbo].[Question] ( [QuestionID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireID] INT NOT NULL, [QuestionName] NVARCHAR (250) NOT NULL, PRIMARY KEY CLUSTERED ([QuestionID] ASC), CONSTRAINT [FK_Question_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID]) ); CREATE TABLE [dbo].[Option] ( [OptionID] INT IDENTITY (1, 1) NOT NULL, [QuestionID] INT NOT NULL, [OptionName] NVARCHAR (150) NOT NULL, PRIMARY KEY CLUSTERED ([OptionID] ASC), CONSTRAINT [FK_Option_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID]) ); CREATE TABLE [dbo].[Response] ( [ResponseID] INT IDENTITY (1, 1) NOT NULL, [QuestionnaireID] INT NOT NULL, [QuestionID] INT NOT NULL, [Val] NVARCHAR (150) NOT NULL, [OptionID] INT NULL, PRIMARY KEY CLUSTERED ([ResponseID] ASC), CONSTRAINT [FK_Response_Option] FOREIGN KEY ([OptionID]) REFERENCES [dbo].[Option] ([OptionID]), CONSTRAINT [FK_Response_Question] FOREIGN KEY ([QuestionID]) REFERENCES [dbo].[Question] ([QuestionID]), CONSTRAINT [FK_Response_Questionnaire] FOREIGN KEY ([QuestionnaireID]) REFERENCES [dbo].[Questionnaire] ([QuestionnaireID]) );

Current data:

insert into questionnaire values ('ASP.NET questionnaire'); insert into questionnaire values('TEST questionnaire'); insert into question values (2, 'rate our services'); insert into question values (2, 'On scale from 1 to 5, how much youre sleepy?'); insert into question values (2, 'how are you today'); insert into [Option] values (1, 'good'); insert into [Option] values (1, 'bad'); insert into [Option] values (1, 'medium'); insert into [Option] values(2, '1'); insert into [Option] values(2, '2'); insert into [Option] values(2, '3'); insert into [Option] values(2, '4'); insert into [Option] values(2, '5'); insert into [option] values (3, 'fine'); insert into [option] values (3, 'great'); insert into [option] values (3, 'not bad'); insert into [option] values (3, 'bad'); insert into response values(2, 1, 'good', 1); insert into response values(2, 1, 'good', 1); insert into response values(2, 1, 'bad', 2); insert into response values(2, 1, 'good', 1); insert into response values(2, 2, '1', 4); insert into response values(2, 2, '3', 3); insert into response values(2, 2, '4', 5); insert into response values(2, 2, '5', 8);

Desired output

SQL Fiddle

Sql Fiddle

最满意答案

如果要display all the options and if there are no responses for them ，则需要使用LEFT JOIN

编辑

我已根据您的SQL小提琴更新了答案。 它适用于SQL Fiddle ，可为您提供所需的输出。

SELECT Q.QuestionName AS Question, A.OptionName AS [Option], COUNT(R.OptionID) AS Responses FROM Question AS Q INNER JOIN [Option] AS A ON A.questionID = Q.questionID LEFT JOIN Response AS R ON Q.QuestionID = R.QuestionID AND R.OptionId=A.Optionid WHERE (Q.QuestionnaireID = 2) GROUP BY Q.QuestionID, Q.QuestionName, A.OptionName ORDER BY Q.QuestionName,A.OptionName

You need to use a LEFT JOIN, if you want to display all the options and if there are no responses for them like

EDIT

I have updated the answer based on your SQL fiddle. It works in SQL Fiddle and gives you your desired output.

SELECT Q.QuestionName AS Question, A.OptionName AS [Option], COUNT(R.OptionID) AS Responses FROM Question AS Q INNER JOIN [Option] AS A ON A.questionID = Q.questionID LEFT JOIN Response AS R ON Q.QuestionID = R.QuestionID AND R.OptionId=A.Optionid WHERE (Q.QuestionnaireID = 2) GROUP BY Q.QuestionID, Q.QuestionName, A.OptionName ORDER BY Q.QuestionName,A.OptionName