Return data from several tables in database

Question

I need some help getting data from several tables.

This is the tables I have:

_______________   ______________   ___________  _______________  _____________
|_tblUsers____|   |_tblAnswers__|  |_tblAlt__|  |_tblQuestion_|  |_survey_____|
| userID      |   | answerAltID |  | altID   |  | questID     |  | surveyID   |
| username    |   | userID      |  | altText |  | questText   |  | surveyName |
|_____________|   |_____________|  |_questID_|  |_surveyID____|  |____________|

TblUsers have a list of users in the system, tblAnswers have all answers that has been given from users, tlbAlt holds alternatives for a question, and tblQuestion has the questions. There is one more table called tblSurveys, but that's not needed here as the ID is mentioned in the tblQuestion.

This is what I have so far:

SELECT
tblQuestion.questText,
tblAlt.altText,
Count(tblAnswers.answerID) as answers_count,
(SELECT COUNT(answerID) FROM tblAnswers, tblAlt 
WHERE tblAnswers.answerAltID = tblAlt.altID 
AND tblAlt.questID = " & CInt(questionID) & ") as total_count
FROM tblAlt, tblQuestion
LEFT JOIN tblAnswers ON (tblAlt.altId = tblAnswers.altID)
WHERE tblAlt.questID = " & CInt(questionID) & "
GROUP BY tblAlt.altText;

This returns rows like this:

| What is blablabla? | The answer is... | 2 (has answered) | 10 (total answers) |

This unfortunately only returns all rows for one question. Is there a way to get all rows that is a part of same survey (based on surveyID)?

If want the output to be like this:

| What is blablabla? | The answer is... | 2 (has answered) | 10 (total answers) | Name of Survey |

I want to return ALL alternatives (with how many answered, total answers, related question and survey).

---

Update:

This my input:

SELECT tblalternativ.altTekst, tblalternativ.altID, Count(tblsvar.svarAltID) as antSvar, 
(SELECT COUNT(*) FROM tblsvar, tblalternativ 
WHERE tblsvar.svarAltID = tblalternativ.altID 
AND tblalternativ.altSpmID = " & CInt(lblQuestion.Tag) & ") as antTotal, 
(SELECT Count(*) FROM tblalternativ WHERE altSpmID = " & CInt(lblQuestion.Tag) & ") as spmTotal 
FROM(tblalternativ) LEFT JOIN tblsvar ON (tblalternativ.altId = tblsvar.svarAltID) 
WHERE(tblalternativ.altSpmID = " & CInt(lblQuestion.Tag) & ")
GROUP BY tblalternativ.altTekst ORDER BY tblalternativ.altID ASC

My output:

altTekst    altID   antSvar antTotal    spmTotal
Black         83         1      3              5
Green         84         1      3              5
Yellow        85         1      3              5
White         86         0      3              5
Pink          87         0      3              5

But this only show statistics for one question. I want to show for all questions in one survey. So I need to get all altTekst for that survey, question name, and ID of survey.

I want:

spmTekst      altTekst  altID   antSvar antTotal    spmTotal   evalID
What is...    Black         83         1        3          5        1
What is...    Green         84         1        3          5        1
What is...    Yellow        85         1        3          5        1
What is...    White         86         0        3          5        1
What is...    Pink          87         0        3          5        1
Who is....    The king      88         2        3          3        1
Who is....    The pope      89         0        3          3        1
Who is....    The president 90         1        3          3        1
Which....     Shoe          91         2        3          2        1
Which....     Hat           92         1        3          2        1

In other words, I want the statistics from all questions in same survey (based on evalID).

Answer 1

To return all questions, answer text, count ofusers with that answer, and total answers provided; per survey.

Select TQ.QuestText, tAlt.altText, count(*) as Answers_count, suM(mTemp.Cnt) as total_count
FROM tblQuestion tq
LEFT JOIN tblAlt talt on Talt.QuestID = TQ.QuestID
LEFT JOIN tblAnswers ta on ta.AnswerAltID = talt.AltID
LEFT JOIN tblUsers tu ON Ta.UserID = TU.UserID
LEFT join tblAnswers ta2 on ta2.answeraltId = talt.altID
LEFT JOIN 
  (SELECT COUNT(*) cnt, questID 
   FROM tblAnswers 
   INNER JOIN tblAlt on AltID = AnswerAltID
   group by questID) mTemp 
  on mTemp.QuestID = talt.QuestID
WHERE tQ.SurveyID = 123 --Change this to your value
Group by TQ.QuestText, TAlt.AltText

It's just a bunch of left joins vs inner joins; and i abstracted out the counts for each table once so it should be faster instead of doing a sub select on each row. This way it does it once for all rows and is done.

Answer 2

Try this(Not at all optimized, just added the survey part to it):

SELECT    tblQuestion.questText, tblAlt.altText, 
          Count(tblAnswers.answerAltID) AS answers_count,
           (SELECT COUNT(answerAltID) FROM tblAnswers, tblAlt 
           WHERE  tblAnswers.answerAltID = tblAlt.altID AND 
                  tblAlt.questID = " & CInt(questionID) & ") as total_count,
          survey.surveyName
FROM      survey, tblQuestion, tblAlt
LEFT JOIN tblAnswers ON (tblAlt.altId = tblAnswers.answerAltID)
WHERE     tblAlt.questID = " & CInt(questionID) & " AND 
          tblQuestion.surveyID = survey.surveyID
GROUP BY  tblAlt.altText;

Edit: Try this then:

SELECT    tblQuestion.questText AS spmTekst, tblAlt.altText AS altTekst, 
          tblAlt.altID, 
          Count(tblAnswers.answerAltID) AS antSvar,
          COUNT(tblAlt.altID) AS antTotal,
          COUNT(tblQuestion.questID) AS spmTotal,
          survey.surveyID AS evalID
FROM      tblQuestion 
JOIN      survey ON (survey.surveyID = tblQuestion.surveyID)
JOIN      tblAlt ON (tblAlt.questID = tblQuestion.questID)
LEFT JOIN tblAnswers ON (tblAnswers.answerAltID = tblAlt.altID)
WHERE     tblAlt.questID = " & CInt(questionID) & " AND -- what really is this? review this
          survey.surveyID = '123' -- the value u want
GROUP BY  tblAlt.altText
ORDER BY  tblAnswers.answerAltID;