I'm coding in VBA, using ADO to query an Access-compatible (JET) database. I have the following query, which works just fine:
SELECT
Answers.UserName AS UserName,
COUNT(Answers.Correct) AS CorrectAnswers
FROM
Answers
WHERE
Answers.QuizID=17
AND (SELECT COUNT(A.UserName) FROM Answers A WHERE A.QuizID=17 AND A.UserName=Answers.UserName)=30
GROUP BY Answers.UserName
This is basically a Q&A quiz whereby every answer is written into the Answers table. One column of the Answers table is "UserName", which contains the logon ID of the user who submitted the answer.
However instead of the logon ID, I want to display the user's full name. This is already stored in a separate table, "Users". In the "Users" table there are two columns: UserName and FullName.
Naturally, the correct way to display the full name would be with a join as follows:
SELECT
Users.FullName AS FullName,
COUNT(Answers.Correct) AS CorrectAnswers
FROM
Answers,
Users
WHERE
Answers.QuizID=17
AND (SELECT COUNT(A.UserName) FROM Answers A WHERE A.QuizID=17 AND A.UserName=Answers.UserName)=30
AND Users.UserName=Answers.UserName
GROUP BY Users.UserName
This also works... just as long as the UserName exists in the Users table. However if for whatever reason the user is not registered in the Users table, instead of returning NULL, the record is not selected altogether.
In the "old-style" join syntax, I would solve this by adding a (+) symbol on the relevant line in the above code, so it reads:
AND Users.UserName=Answers.UserName(+)
This would ensure that all records are returned, even if the match does not exist in the Users table. However when I try this, ADO throws up an error: Extra ( in query expression... etc. I'm presuming that this is because ADO does not recognise the (+) syntax.
So the question is either, whether there is a way to get ADO to recognise (+) joins; or how should I write the query in the "new style" join syntax to get it to do what I want?
What I've tried:
SELECT
Users.FullName AS UserName,
COUNT(Answers.Correct) AS CorrectAnswers
FROM
Answers,
RIGHT JOIN Users U on Answers.Username = U.UserName
WHERE
Answers.QuizID=17
AND (SELECT COUNT(A.UserName) FROM Answers A WHERE A.QuizID=17 AND A.UserName=Answers.UserName)=30
GROUP BY Users.FullName
But this doesn't work. It just throws Syntax Error. I have also tried it without the comma following Answers, to no avail.
There are plenty of online resources telling people how to use the new-style joins. However I haven't seen anything showing me how to do this properly, where the query includes other clauses in WHERE. Indeed, I find that all new-style join examples are relatively simplistic where it's demonstrated how to perform a single join on two tables - where there are no other elements to the query. However when the query is even a touch more complicated, it's unclear how the syntax should be used.
What you need is a LEFT join of Answers to Users based ON the columns UserName in both tables.
This means that all rows of Answers will be returned even if there are no matches in Users.
But there is also another problem: in the SELECT list you have the column FullName although you don't include it in the GROUP BY clause and this is not allowed.
So I included it also in GROUP BY:
SELECT
a.UserName, u.FullName,
COUNT(a.Correct) AS CorrectAnswers
FROM Answers a LEFT JOIN Users u
ON u.UserName = a.UserName
WHERE a.QuizID=17
AND
(SELECT COUNT(aa.UserName) FROM Answers aa WHERE aa.QuizID = a.QuizID AND aa.UserName = a.UserName) = 30
GROUP BY a.UserName, u.FullName
Although I don't know the reason for the subquery in the WHERE clause, I believe it can be moved in a HAVING clause like this:
SELECT
a.UserName, u.FullName,
COUNT(a.Correct) AS CorrectAnswers
FROM Answers a LEFT JOIN Users u
ON u.UserName = a.UserName
WHERE a.QuizID=17
GROUP BY a.UserName, u.FullName
HAVING COUNT(*) = 30