What would be the SQL query for my problem?

Question

I need help with a SQL query for room within an android app. There is a table questions. This table has 3 columns:

id question id_sub_cateory

Furthermore there is table_sub_categories with folloing columns:

id sub_category id_category

And table_categories:

id category id_subject

Additionally there is a table_subject:

id subject id_questionnaire

Last but not least table_questionnaire:

id questionnaire

Now I would like to have a SQL query to get following information:

table_questionnaire.id table_questionnaire.questionnaire questionCount subjectCount

So basically a list which lists all questionnaires with number of questions and subjects included. What would be the SQL query for that?

EDIT: So far I have used this query:

SELECT table_questionnaire.id AS 'id', table_questionnaire.questionnaire_name AS 'name', " +
            "COUNT(table_question.id_questionnaire) AS 'questionCount', COUNT(DISTINCT table_question.subject) AS 'subjectCount' " +
            "FROM table_questionnaire " +
            "LEFT JOIN table_question ON table_questionnaire.id = table_question.id_questionnaire GROUP BY table_questionnaire.id"

But this one is an old version because I saved each question with an ID of questionnaire, subject, category and sub_category. I recognized that this information is redundant. Now I save only the ID of sub_category. But now I have to change the query. I just don't know how!?

Answer 1

This is the query which works:

SELECT
                table_categories.id,
                table_categories.id_subject,
                table_categories.category,
                table_sub_categories.id AS 'id_sub_category',
                table_sub_categories.id_category,
                table_sub_categories.subCategory,
                COUNT(id_category) AS 'questionsCount'
            FROM table_question
                LEFT JOIN table_sub_categories ON table_question.id_sub_category = table_sub_categories.id
                LEFT JOIN  table_categories ON table_categories.id = id_category 
            WHERE table_categories.id_subject = :subjectID 
            GROUP BY table_categories.id