-2

I have to make a query to:

show a view which shows the results of counting all the employee dedications per employee area, listed by the area names in alphabetical order.

$this->db->query('create temporary table temp as (select dedication.employee_employeeID, dedication ID, COUNT(area) AS TotalFrequency from dedication, employees where dedication.employee_employeeID = dedication group by dedication.employee_employeeID)');

However, doesn't seem to be working on my website?

here is my relationships:

https://i.stack.imgur.com/gDdN5.png

Petru Maika
  • 3
  • 3

1 Answers1

0

You probably want a SQL statement like this:

create temporary table temp as (
  select
    e.employee_ID,
    e.full_name,
    a.business_ID,
    a.business,
    count(*) as dedications
  from employee e
  join dedication d on d.employee_employeeID = e.employee_ID
  join employee_area ea on ea.employee_employeeID = e.employee_ID
  join business_area a on a.business_ID = ea.business_area_business_ID
  group by e.employee_ID, a.business_ID
  order by a.business
)
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • 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 'd.employee_employeeID = e.employee_ID join employee_area ea on ea.employee_employeeID' at line 9 – Petru Maika Mar 14 '19 at 19:05
  • Not an obvious error. The syntax seems to be good. Check the table and column names. They need to be 100% accurate: 1) should be the exact same lower/upper case; 2) if they have special characters (spaces for example) enclose identifiers with back ticks`. – The Impaler Mar 14 '19 at 19:38