SELECT Detail.idThema, Detail.txtAfdeling, intStartPct, count(Detail.idRespondent)
FROM (SELECT idThema, txtAfdeling, idRespondent, Score FROM AnotherTable) AS Detail
LEFT OUTER JOIN enq_ThemaAdvies ON Detail.idThema = enq_ThemaAdvies.idThema AND Score >= intStartPct AND Score <= intEindPct
GROUP BY Detail.idThema, intStartPct, Detail.txtAfdeling

quote:

---

Originally posted by beheer25

Hi,

The application thi is for is a survey tool. I calculate a score for each respondent of a survey and count all scores that fall in a specific range by using the extra conditions in the join. There are no nulls in the Detail resultset. The complete resultset of the query is OK, but the ranges between intStartPct and intEindPct which do not have any respondents with a corresponding score are not shown in the resultset.

What I get:
idThema; txtAfdeling; intStartPct; count(idRepondent)
1; Department1; 0; 2
1; Department1; 21; 3
1; Department1; 81; 4

What I need:
idThema; txtAfdeling; intStartPct; count(idRepondent)
1; Department1; 0; 2
1; Department1; 21; 3
1; Department1; 41; NULL
1; Department1; 61; NULL
1; Department1; 81; 4

Hope this makes it any clearer...

Mark

---

		SELECT Detail.idThema, txtAfdeling, intStartPct, count(*)
		FROM (
		SELECT idThema, enq_Respondent.idRespondent, txtAfdeling, enq_Vraag.idVraag, convert(real,intScore) / convert(real, maxScore) * @intMaxScore as Score
		FROM enq_Vraag
		INNER JOIN enq_Resultaat ON enq_Resultaat.idVraag = enq_Vraag.idVraag
		INNER JOIN enq_Respondent ON enq_Respondent.idRespondent = enq_Resultaat.idRespondent
		INNER JOIN enq_Persoon ON enq_Persoon.idPersoon = enq_Respondent.idPersoon
		INNER JOIN enq_Antwoord ON enq_Resultaat.idResultaat = enq_Antwoord.idResultaat
		INNER JOIN enq_MeerkeuzeAntwoord ON enq_Antwoord.intAntwoord = enq_MeerkeuzeAntwoord.idMeerkeuzeantwoord
		INNER JOIN enq_Pagina ON enq_Pagina.idPagina = enq_Vraag.idPagina
		INNER JOIN enq_VraagSubthema ON enq_VraagSubthema.idVraag = enq_Vraag.idVraag
		INNER JOIN enq_Subthema ON enq_VraagSubthema.idSubthema = enq_Subthema.idSubthema
		INNER JOIN (
			SELECT enq_Vraag.idVraag, max(intScore) as maxScore
			FROM enq_Vraag
			INNER JOIN enq_MeerkeuzeAntwoord ON enq_Vraag.idVraag = enq_MeerkeuzeAntwoord.idVraag
			INNER JOIN enq_Pagina ON enq_Pagina.idPagina = enq_Vraag.idPagina
			INNER JOIN enq_VraagSubthema ON enq_VraagSubthema.idVraag = enq_Vraag.idVraag
			INNER JOIN enq_Subthema ON enq_VraagSubthema.idSubthema = enq_Subthema.idSubthema
			WHERE idEnquete = @idEnquete
			GROUP BY enq_Vraag.idVraag)
		AS MaxPerVraag
		ON MaxPerVraag.idVraag = enq_Vraag.idVraag
		WHERE enq_Pagina.idEnquete = @idEnquete
		AND enq_Respondent.blnAfgerond = 1
		AND enq_Persoon.intAfdelingNiveau = @intOrganisatieNiveau
		) as Detail
		LEFT OUTER JOIN enq_ThemaAdvies ON Detail.idThema = enq_ThemaAdvies.idThema AND Score >= intStartPct AND Score <= intEindPct
		GROUP BY Detail.idThema, intStartPct, txtAfdeling