joins going wrong - SQL Server Forums

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)

joins going wrong

Author

Topic

stsql
Starting Member

19 Posts

Posted - 2006-04-03 : 13:36:43

Hi all,

I'm stuck on a SQL query that I've trying to get my head round. I'm pretty sure that I've gone a way off course with it and was hoping someone would be able to help.

I have three tables (with example data)
surveyQuestions

surveyMulti
- this table is only used when the answerTypeID=4, with 1 being a yes/no answer and 2 being a sliding scale answer 1/2/3/4/5

surveyAnswers

I'm trying to get a query to give results like:
=========
Results Start
=========
Do you have a favorite colour?
Yes 2
No 0

Pick your two favorite colours?
Yellow 0
Red 2
Green 1
Other 1
=========
Results End
=========

My query so far looks like this


SELECT DISTINCT(SQ.question)
     , SQ.questionID
     , SQ.priority
     , SM.answer
	 , min(SA.answer) as thisAnswer
     , COUNT(SA.answer) as theAnswer
  FROM ((
       surveyQuestions as SQ
LEFT 
  JOIN surveyMulti as SM 
    ON SQ.questionID = SM.questionID
       )
LEFT 
  JOIN surveyAnswers as SA
    ON SQ.questionID = SA.questionID
       )
 WHERE SQ.active = '1'
   AND SM.active = '1'
   AND SA.active = '1'
 GROUP
    BY SQ.question
     , SQ.questionID
     , SQ.priority
     , SM.answer
     , SA.answer
ORDER 
    BY SQ.priority
	 , SM.answer

So I guess my first question is can the above result be created from the one query? Next, if it can, where am I going wrong?
I know there is quite a bit missing on this query and that I'm in over my head, so thanks in advance for any help,

Richard

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 14:01:13

quote:
Originally posted by stsqlSo I guess my first question is can the above result be created from the one query?

With enough hackneyed SQL, you can do just about anything.

quote:
Originally posted by stsqlNext, if it can, where am I going wrong?

By trying to do this in the first place. You are committing one of the cardinal sins of database application development: formatting output should be done by your reporting tool, not by the server.

stsql
Starting Member

19 Posts

Posted - 2006-04-04 : 06:10:11

Thanks blindman, that makes a lot more sense.

Richard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources