Three Tables and a Group By

[BatGrrrL]

Hi Guys,

I need some help with a couple of tables and a query. I am building a test generator for our teachers here (we are a school) and I have set up my tables as follows:

QCat:
QCatID - PK
QCat

QTypes: (Question Types eg, True/False, Multi Choice)
QTypeID - PK
QType

Questions:
QID - PK
Question
QTypeID - FK
CatID - FK

What I would like out of my query, is a join and then to receive the Categories Name and the Number of questions for each Question Type under that (if any):

Eg:
Maths
- T/F - 4
- M/C - 2

English
- M/C - 5

Here is the join and the query that doesn't work. I'm sure I have done it wrong but I don't understand it enough to fix it. This only gives me one line of cells. Can someone please help?

SELECT QCat.QCat, QType.QTypeID
FROM QType RIGHT JOIN (Cat LEFT JOIN Questions ON Cat.QCatID = Questions.QCat) ON QType.QTypeID = Questions.QType
GROUP BY qcatid.qcatid, QType.QTypeID
ORDER BY QCat.QCat, QType.QTypeID;


Cheers
Batty

 

[RickSpr]

First, although this isn't causing the problem, it seems to me that you probably want INNER JOIN instead of LEFT JOIN and RIGHT JOIN. As long as each question has both a category and a type, the results are the same, but using LEFT JOIN and RIGHT JOIN imply that you expect some categories to have no questions, and that you still want a row for such categories. INNER JOIN would make more sense to somebody else reading your code.

Next, the query you present won't work at all, because your GROUP BY refers to "qcatid.qcatid", and there is no qcatid table. I'm sure it's just a typo, but typos like that can be very confusing. It's much better if you cut and paste the actual code, rather than retyping it. I've seen it happen that the questioner's typo actually fixed the problem without him realizing it, and the rest of us were stumped because we couldn't see anything wrong with the code.

In this case, I assume your GROUP BY actually refers to QCat.QCat. All you need to do is add "Count(*)" to your SELECT list. That will give you the number of questions for each category and type.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BatGrrrL]

Thanks Rick,

I changed the typo in the code (I had cut and pasted it), made the joins inner types and added the count(*) and it's closer to working.

When I run the query, it pops up a box asking for the qcat and the qtypeid. So I type in 1 and 1 and it returns one line with the two numbers I have typed in and the total of questions in the database.

CatName | QTypeID | Totaller
1 1 7

SELECT [QCat].[QCat] AS CatName, QType.QTypeID, Count(*) AS Totaller
FROM QType INNER JOIN (Cat INNER JOIN Questions ON Cat.QCatID = Questions.QCat) ON QType.QTypeID = Questions.QType
GROUP BY QType.QTypeID, qcat.qcatid
ORDER BY qcat.qcatid, QType.QTypeID;

Also, If I chance QType.QTypeID to QType.QType in the select criterea it tells me it is not part of the aggregate function.

*smiles* I'm so doing it wrong and I cant work out why

Thank you
BatGrrrL

 

[RickSpr]

Darned if I know. There are a lot of discrepancies between the names you use in the query and the names you gave for tables and fields in your first post. You seem to be confusing table names with column names in the query. And you're trying things without really understanding what you're doing.

Let's start over. Give me the correct names of your tables and fields. In return, I'll give you a detailed explanation of how to build your query, and what each element means.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BatGrrrL]

Woo Hoo,

Thanks Rick!

I was getting confused with the crud names I'd given the tables and fields. I renamed them and rewrote the query with your suggestions and it works a treat!

SELECT Count(*) AS NumberOfQuestions, Category.CategoryName, QuestionTypes.QuestionTypeName
FROM QuestionTypes
INNER JOIN (Category INNER JOIN Questions ON Category.CategoryID = Questions.CategoryID) ON QuestionTypes.QuestionTypeID = Questions.QuestionTypeID
GROUP BY Category.CategoryName, QuestionTypes.QuestionTypeName;

Cheers!

Batty

 

[RickSpr]

Glad I could help you work it out.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein