Query - "Wrong" Data Returned

[a98dsu98sad8sua]

Hi all,

I am trying to use this SQL query to output the results of a questionnaire into a report:

SELECT Calls.CallID, Calls.CallTime, Calls.CallDate, Calls.CallDuration, Calls.CustomerName, Calls.CustomerNumber, Calls.AddedBy, Calls.FromComputer, Campaigns.CampaignName, Contracts.ContractName, Sections.SectionName, Managers.ManagerName, Staff.StaffName, Questions.Title, MonitoringTypes.MonitoringType, Responses.ResponseName
FROM (Sections INNER JOIN (Managers INNER JOIN (Contracts INNER JOIN ((Campaigns INNER JOIN (Responses INNER JOIN Questions ON Responses.ResponseTypeID = Questions.ResponseTypeID) ON Campaigns.CampaignID = Questions.CampaignID) INNER JOIN Staff ON Campaigns.CampaignID = Staff.CampaignID) ON Contracts.ContractID = Staff.ContractID) ON Managers.ManagerID = Staff.ManagerID) ON Sections.SectionID = Questions.SectionID) INNER JOIN (MonitoringTypes INNER JOIN Calls ON MonitoringTypes.MonitoringTypeID = Calls.MonitoringTypeID) ON Staff.StaffID = Calls.StaffID;

but the data it is returned is wrong! It has put for example both responses as an answer for a question, for example:

Is this a question? - Yes
Is this a question? - No

Obviously, it's only supposed to output the right one, but it seems to be printing the choices instead.

Sorry I can't explain it any more than that it is hard!

Any suggestions?

T

 

[Golom]

Without knowing your table structures (other than what can be deduced from the SQL) its difficult to say what the problem may be.

Just as a guess, I would conclude that table Responses contains all possible responses to the question so of course all of them are being listed. You would need either a table containing only the response that was given OR some field in the Responses table that distinguishes which of the available responses was selected.

 

[a98dsu98sad8sua]

But ResponseID in Table Responses is a foreign key in Sessions!

 

[a98dsu98sad8sua]

This works:

SELECT [Staff.StaffName] AS StaffName, [Calls.CallTime] AS CallTime, [Calls.CallDate] AS CallDate, [Questions.Title] AS Title, [Responses.ResponseName] AS Response, [Calls.CallID] AS CallID, [Calls.CallDuration] AS Duration, [Calls.CustomerName] AS CustomerName, [Calls.CustomerNumber] AS CustomerNumber, [Calls.AddedBy] AS AddedBy, [Calls.FromComputer] AS ComputerName, [Managers.ManagerName] AS Manager, [Sections.SectionName] AS [Section], [Contracts.ContractName] AS Contract, [Campaigns.CampaignName] AS Campaign, [MonitoringTypes.MonitoringType] AS MonitoringType
FROM (Managers INNER JOIN (Contracts INNER JOIN Staff ON Contracts.ContractID=Staff.ContractID) ON Managers.ManagerID=Staff.ManagerID) INNER JOIN (Sections INNER JOIN (Responses INNER JOIN ((Campaigns INNER JOIN Questions ON Campaigns.CampaignID=Questions.CampaignID) INNER JOIN (MonitoringTypes INNER JOIN (Calls INNER JOIN Sessions ON Calls.CallID=Sessions.CallID) ON MonitoringTypes.MonitoringTypeID=Calls.MonitoringTypeID) ON Questions.QuestionID=Sessions.QuestionID) ON Responses.ResponseID=Sessions.ResponseID) ON Sections.SectionID=Questions.SectionID) ON Staff.StaffID=Calls.StaffID;