Cannot figure out how to join last bit of data

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

Transact-SQL (2000)

Cannot figure out how to join last bit of data

Author

Topic

morleyz
Starting Member

17 Posts

Posted - 2007-12-10 : 09:19:58

I am trying to write the query for a report that is part of a review system database. The report needs to pull all of the reviewers responses for a particular reviewee and order them according to the template layout.

The dynamic information for this particular query will be the reivew group ID # and the reviewed ID # used in the WHERE clause, I just dropped to values in there for my own reference.

A quick overview of the data structure:
Table reviewGroup - contains details about a particular review such as name, start date, end date, and the review template ID #
Table reviewGroupMatch - maps relationship of reviewer to reviewee
Table employees - contains individual employee information such as name, title, etc.
Table questions - contains details of each question such as question and the type of question (like yes/no, true false, etc).
Table templateMatch - contains the format of the template to display to end users and for reports. Data matches all of the question ID #'s, the order of that particular question and which template it belongs to.
Table responses - contains individual responses formated as reviewGroupMatch ID #, QuestionID and response.

The SQL Query that I built that is about 90% of the information I need:

select questions.question, questions.type, headingName, templateOrder,
reviewer.FN as reviewerFN, reviewer.LN as reviewerLN,
reviewed.FN as reviewedFN, reviewed.LN as reviewedLN
FROM (((((templateMatch
INNER JOIN reviewGroup ON templateMatch.templateID = reviewGroup.templateID)
INNER JOIN questions ON templateMatch.QuestionID = questions.ID)
INNER JOIN reviewGroupMatch ON reviewGroup.ID = reviewGroupMatch.reviewGroupID)
INNER JOIN employees reviewer ON reviewGroupMatch.reviewerID = reviewer.ID)
INNER JOIN employees reviewed ON reviewGroupMatch.reviewedID = reviewed.ID)
WHERE reviewGroup.ID = 48 AND reviewGroupMatch.reviewedID = 78
ORDER BY templateOrder

This returns a list of each reviewer, the question the response belongs to and the type of question all ordered according to the template structure. This part of the query works exactly how I want it to.

Now, where I can't get it to work is when I try to join in the responses. I'm am trying to use a LEFT JOIN because I want a record displayed for each reviewer even if it's null or doesn't exist. So what I tried is:

select questions.question, questions.type, headingName, templateOrder,
reviewer.FN as reviewerFN, reviewer.LN as reviewerLN,
reviewed.FN as reviewedFN, reviewed.LN as reviewedLN
FROM ((((((templateMatch
INNER JOIN reviewGroup ON templateMatch.templateID = reviewGroup.templateID)
INNER JOIN questions ON templateMatch.QuestionID = questions.ID)
INNER JOIN reviewGroupMatch ON reviewGroup.ID = reviewGroupMatch.reviewGroupID)
INNER JOIN employees reviewer ON reviewGroupMatch.reviewerID = reviewer.ID)
INNER JOIN employees reviewed ON reviewGroupMatch.reviewedID = reviewed.ID)
LEFT JOIN responses ON reviewGroupMatch.ID = responses.reviewGroupMatchID AND templateMatch.questionID = responses.questionID)
WHERE reviewGroup.ID = 48 AND reviewGroupMatch.reviewedID = 78
ORDER BY templateOrder

When I run this query, I get an "SQL expression not supported" from Access. If is use an INNER JOIN instead of a LEFT JOIN, I get exactly what I need, but only for reviewers that responded (not the null response records). I know this is what I should expect from the INNER JOIN, but I don't know how to get those null responses any differently than I tried.

Can someone assist me in joining my response data into the working 1st query?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-10 : 09:24:33

If it is Access, maybe OUTER JOIN works (though not 100% sure, ask in the Access Forum).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 09:25:51

Are you using Microsoft SQL Server or Microsoft Access?

E 12°55'05.25"
N 56°04'39.16"

morleyz
Starting Member

17 Posts

Posted - 2007-12-10 : 09:29:27

Sorry. I guess I left the Access error in there. I've tried it with both to see if it was Access specific. I get an error with SQL server also. I'll have to regenerate the error for SQL. This is the only app we have with an Access DB...I guess I forgot before I posted here.

morleyz
Starting Member

17 Posts

Posted - 2007-12-10 : 09:48:00

OK. I lied. It does work in SQL Server. I don't know what I did that generated the error in SQL server before. I'll have to go query the access experts and see why it won't work in Access.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources