Closed as solution was found
try this
select Department_name, count(*) as num_of_module from Module
WHERE EXISTS (SELECT * FROM HISTORY WHERE MODULE.Modlue_code=HISTORY.Module_code)
group by Department_name
I had tried that previously and it threw this error,
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM MODULE WHERE EXISTS (SELECT * FROM HISTORY WHERE MODULE.Module_code=HISTORY' at line 1
This SQL server Transact-SQL fourm
any way try this
select Department_name, count(*) as num_of_module from Module
WHERE Modlue_code in (SELECT Module_code FROM HISTORY )
group by Department_name
There we go, that one did the trick, I thought about using IN bit i thought in theory that the two statements were in fact identical