I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:
USE UNAMupdates;
with CTE as
( SELECT tbl_modules.module_code
FROM tbl_student
JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
WHERE tbl_student.student_number='1002')
SELECT tbl_notifications.module_name,tbl_notifications.message,tbl_notifications.staff_username,tbl_notifications.date_time
FROM CTE JOIN tbl_notifications
ON cte.module_code = tbl_notifications.module_code
ORDER BY tbl_notifications.date_time DESC
What I am trying to achieve is to query all modules/subjects taken by a single student and then finding out if any notifications/news items are available for that subject.