Database Design Question

I have 5 tables Section, Question, UserQuestionnaire, Answer, User

Section Table
-----

SecId SecName
1 First Section Name
2 Second Section Name
3 Third Section Name

Question
----

QID QName SecId
1 how are you? 1
2 who are you? 3
3 where are you? 2
4 do i know you? 1

Users
--

UId Email
1 email@.email.com
2 user@.emailme.com

UserQuestionnaire
-------

UQId UId SecId DateCreated
1 1 1 20/01/04
2 1 2 20/01/04

Answer
---

AId Answer QId UQId
1 i'm fine 1 1
2 no, you dont know me. 4 1
3 i'm in las vegas 3 2

Now here comes my question:

I want to show all the secname from section table, datecreated from UserQuestionnaire table for a particular user. If a user hasn't answered a particular section then I want to display "None" in place of datecreated. In the above example UId has done section 1 and 2 but he hasn't done section 3. So I want to display Datecreated for Section 1 and 2 but none for section 3. how to do this? what will be my query?The way to think about this is -- you want to return all of the sections and show where the user has answered the questions for the section. So the table in the From clause, the table for which all of the records will be returned, should be Sections. And since you want to show the Section whether or not there is a UserQuestionnaire record, the Join type should be a Left Outer Join.

This is how you might go about writing the query:

SELECT
 Sections.secname,
 ISNULL(CAST(UserQuestionnaire.datecreated AS varchar(20)),'None')
FROM
 Sections
LEFT OUTER JOIN
 UserQuestionnaire ON UserQuestionnaire.SecID = Sections.SecID AND UserQuestionnaire.UID = 1

If you needed to broaden the query to show a user and the sections whether or not he has answered any of the questions, the query would look like this:

SELECT
 Users.Uid,
 Sections.secname,
 ISNULL(CAST(UserQuestionnaire.datecreated AS varchar(20)),'None')
FROM
 Users
CROSS JOIN
 Sections
LEFT OUTER JOIN
 UserQuestionnaire ON UserQuestionnaire.SecID = Sections.SecID AND Users.UId = UserQuestionnaire.UId
WHERE
 Users.UID = 1

This second option is more flexible.

Terri|||Thank you terri

That was the query I was really after. It works great.

cheers|||I have another question regarding queries from the above design.

I want to display all the questions and answers for a particular section and for a particular user. If a user hasn't answered any questions I want to display a empty string in place of answers.

how to do this? I tried left outer join but it returns all the questions and answers for a particular user and a empty string where he hasn't answered but I'm unable to filter it to show only a particular section.|||I knew this was coming ;-)

Here is something that should get you started. You might want to change the LEFT OUTER JOIN to Question to an INNER JOIN if you don't want the section to show up if no questions have been created for it.

SELECT
 Sections.SecID,
 Sections.secname,
 Users.Uid,
 ISNULL(CAST(UserQuestionnaire.datecreated AS varchar(20)),'None'),
 Question.Qid,
 ISNULL(Question.Qname,'No Questions') AS Qname,
 ISNULL(Answer.Answer,'') AS Answer
FROM
 Users
CROSS JOIN
 Sections
LEFT OUTER JOIN
 Question ON Question.SecID = Sections.SecID
LEFT OUTER JOIN
 UserQuestionnaire ON UserQuestionnaire.SecID = Sections.SecID AND UserQuestionnaire.UId = Users.UId
LEFT OUTER JOIN
 Answer ON Answer.QID = Question.QID AND Answer.UQID = UserQuestionnaire.UQID
WHERE
 Users.UID = 1
ORDER BY
 Sections.SecID,
 Question.QID