cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

SQL Query for 2 questions/responses

We have a general catalog item that has 29 questions (its to review new desktop/laptop/SaaS) software for the company). Our auditors have asked for a report of all software in this item where the user is asking for SaaS  so I'm trying to build a query that would provide the software title (question 1) and if the user answered yes to to to question 2.  I'm using the wd_question and wd_response tables instead of the wd_responsequestionaudittrail because they just want the software title with some other information (request id, datetime, approval status etc)

I'm having a bit of trouble when I want to show the software title and use that question in the query.  It reports 979 rows.  If I use the the question/response I get 52 rows (which is correct) but then I don't get the title and have to do some lookups in excel to match the two sheets.

FROM WD_PackageRequests
INNER JOIN WD_WebPackages ON WD_PackageRequests.PackageID_FK = WD_WebPackages.PackageID
INNER JOIN WD_User ON WD_PackageRequests.UniqueUserName=WD_User.UniqueName
Inner Join WD_Response ON WD_PackageRequests.RequestID=WD_Response.RequestID_FK
Inn Join WD_Question ON WD_Response.QuestionID_FK=WD_Question.QuestionID

WHERE WD_webpackages.packageid in (3094, 3116)
and wd_packagerequests.Authorized in (0,1,2,3)
and wd_response.QuestionID_FK='1459' and wd_response.response = 'yes'

Is there a way to get both without using a stored procedure and temp tables?

(1) Solution

What about something like this?

 

SELECT [OrderDate] AS [Order Date]
      ,[OrderName] AS [Order ID]
      ,[RequestID] AS [Request ID]
      ,[RequesterEmail] AS [Requester]
      ,[ResponseValue] AS [SaaS Title]
  FROM [vMyRequestsCatalogUpdated] req
  JOIN [vResponse] resp ON [req].[RequestID]=[resp].[RequestID_FK]
 WHERE [req].[PackageID] IN (3094,3116)
   AND [req].[Authorized] IN (0,1,2,3)
   AND [resp].[QuestionID] = <Software_Title_Question_ID>
   AND [req].[RequestID] IN
       (SELECT [RequestID_FK] FROM [vResponse] WHERE [QuestionID]=1459 AND [ResponseValue]='yes')

 

You'll need to replace <Software_Title_Question_ID> with the integer ID for your first question, since I couldn't get that from your original post.

Note: I'm using views here instead of tables, which is probably a better practice than writing queries directly against the tables.

 

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

View solution in original post

(3) Replies

@TeriStevenson 

I'm not an SQL expert but I would try to take the outset in the question or response tables and then use 'left outer join' to reference back to the package request table to get the relevant package title, i.e. something along the lines of

from WD_Response
inner join WD_Question on WD_Response.QuestionID_FK=WD_Question.QuestionID
left outer join WD_PackageRequests ON WD_PackageRequests.RequestID=WD_Response.RequestID_FK
where wd_response.response = 'yes'
and WD_Response.QuestionID_FK=1459

Thanks,

What about something like this?

 

SELECT [OrderDate] AS [Order Date]
      ,[OrderName] AS [Order ID]
      ,[RequestID] AS [Request ID]
      ,[RequesterEmail] AS [Requester]
      ,[ResponseValue] AS [SaaS Title]
  FROM [vMyRequestsCatalogUpdated] req
  JOIN [vResponse] resp ON [req].[RequestID]=[resp].[RequestID_FK]
 WHERE [req].[PackageID] IN (3094,3116)
   AND [req].[Authorized] IN (0,1,2,3)
   AND [resp].[QuestionID] = <Software_Title_Question_ID>
   AND [req].[RequestID] IN
       (SELECT [RequestID_FK] FROM [vResponse] WHERE [QuestionID]=1459 AND [ResponseValue]='yes')

 

You'll need to replace <Software_Title_Question_ID> with the integer ID for your first question, since I couldn't get that from your original post.

Note: I'm using views here instead of tables, which is probably a better practice than writing queries directly against the tables.

 

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

Jim - thanks so much!  I was out a couple of days, just tested and this works great.  I appreciate the help!  I'll be using views for this 🙂