I got 3 tables

| ID   | Name   |
|:---- |:------:| 
| 1    | Brie   | 
| 2    | Ray    | 
| 3    | James  | 

Table2

| ID   | Q_id   | Q_no  | ans |
|:---- |:------:| -----:|----:|
| 1    | 2304.  | 1     |  A  |
| 1    | 2304.  | 2     |  A  |
| 1    | 2305.  | 1     |  C  |
| 2    | 2304.  | 2     |  A  |
| 2    | 2305.  | 1     |  C  |
| 3    | 2304.  | 1     |  A  |
| 3    | 2305.  | 2     |  D  |

Table3
 | Q_id   | Q_no  | correct_ans |
 |:------:| -----:|------------:|
 | 2304.  | 1     |  A          |
 | 2304.  | 2     |  B          |
 | 2305.  | 1     |  C          |
 | 2305.  | 2     |  D          |

I need to print a table with ID, name and count of ans in table 2 where ans matches with correct answer from table 3

| ID   | Name   | ans_count  |
|:---- |:------:| ----------:|
| 1    | Brie   |   2        |
| 2    | Ray    |   1        |
| 3    | James  |   2        |

Select t1.ID, Name, count(t2.ans) as ans_count
from Table1 t1
join Table2 t2 on t1.ID=t2.ID
join Table3 t3 on t2.Q_id=t3.Q_id
where t2.ans=t3.correct.ans and t2.q_no=t3.q_no
group by t1.ID
order by t1.ID

Where am I doing it wrong? Sorry I am new to SQL.

CodePudding user response：

You should always link the tables, with all colums that match

AND the GROUP By should contain all columns that have no aggregation function

SELECT t1.ID,t1.`Name`,COUNT(*) as correct_answers
FROM table1 t1 
  JOIN table2 t2 ON t1.ID = t2.ID
  JOIN table3 t3 ON t2.`Q_id` = t3.`Q_id` AND  t2.`Q_no` = t3.`Q_no`
WHERE t3.`correct_ans` = t2.`ans`
GROUP BY t1.ID,t1.`Name`
order by t1.ID

fiddle

CodePudding user response：

Its still not clear what you are after, but this corrects the obvious issue with the query.

Select t1.ID, Name, count(*) as ans_count
from Table1 t1
join Table2 t2 on t1.ID=t2.ID
join Table3 t3 on t2.Q_id=t3.Q_id
where t2.ans=t3.correct.ans
group by t1.ID, t1.Name  ///<---------------
order by t1.ID

I think you need to group by Id and Name