Returning a Sub-Recordset within a recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning a Sub-Recordset within a recordset

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-10 : 07:47:57

Chris Sebok writes "I'm tearing my hair out trying to get this to work... basically it's a terrible DB design, but it's all I have to work with for now and can't be changed due to dependancies. I'm using SQL Server 2000, and it's hosted on Windows 2000 Advanced Server.

The tables involved are as follows:

* Customers (main customer table)
* DataSubjects (Customer's customers/prospects)
* CustomerDataSubjects (link table to link one DataSubject to multiple Customers)
* CustomerQuestions (Questions that the customer is asking their data subjects)
* CustomerAnswers (The answers to the questions (There are multiple answers per question)
* DataSubjectAnswers (The DataSubject's answers, storing the CustomerAnswer_id from CustomerAnswers)

I know the following won't work, but hopefully you can see what I want to do with it...


-- SQL

SELECT dbo.DataSubjects.*, (
	SELECT CustomerQuestions.CustomerQuestion_Text, CustomerAnswers.CustomerAnswer_Text, 
	DataSubjectAnswers.DataSubjectAnswer_text
	FROM CustomerQuestions
	INNER JOIN CustomerAnswers ON CustomerQuestions.CustomerQuestion_id = CustomerAnswers.CustomerQuestion_id
	RIGHT OUTER JOIN DataSubjectAnswers ON CustomerAnswers.CustomerAnswer_id = DataSubjectAnswers.CustomerAnswer_id
	WHERE (DataSubjectAnswers.DataSubject_id = dbo.DataSubjects.DataSubject_id)
	AND (CustomerQuestions.customer_id = 100012)
) AS Questions
FROM dbo.DataSubjects
INNER JOIN dbo.CustomerDataSubjects ON dbo.DataSubjects.DataSubject_id = dbo.CustomerDataSubjects.DataSubject_id
LEFT OUTER JOIN dbo.Countries ON dbo.DataSubjects.country_id = dbo.Countries.country_id
WHERE (dbo.CustomerDataSubjects.customer_id = 100012)

--End

I want to return a sub recordset for every customer returned - the sub recordset should contain that record's Questions and Answers.

Thanks in advance."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-10 : 09:02:48

Recordsets are square. You can return repeated rows from your outer recordset something like

SELECT dbo.DataSubjects.*, a.*
) AS Questions
FROM dbo.DataSubjects,
INNER JOIN dbo.CustomerDataSubjects ON dbo.DataSubjects.DataSubject_id = dbo.CustomerDataSubjects.DataSubject_id
LEFT OUTER JOIN dbo.Countries ON dbo.DataSubjects.country_id = dbo.Countries.country_id
left outer join (
SELECT CustomerQuestions.CustomerQuestion_Text, CustomerAnswers.CustomerAnswer_Text,
DataSubjectAnswers.DataSubjectAnswer_text, CustomerQuestions.customer_id, DataSubjectAnswers.DataSubject_id
FROM CustomerQuestions
INNER JOIN CustomerAnswers ON CustomerQuestions.CustomerQuestion_id = CustomerAnswers.CustomerQuestion_id
RIGHT OUTER JOIN DataSubjectAnswers ON CustomerAnswers.CustomerAnswer_id = DataSubjectAnswers.CustomerAnswer_id
) as a
on a.customer_id = dbo.CustomerDataSubjects.customer_id
and a.DataSubject_id = dbo.DataSubjects.DataSubject_id
WHERE (dbo.CustomerDataSubjects.customer_id = 100012)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources