-3

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:

enter image description here

Petru Maika
  • 3
  • 3
  • 1
    Please define “not working.” – Sami Kuhmonen Mar 12 '19 at 16:08
  • `interest.staff_staffID = interest.interestID` is this correct? What is the `customers` table doing in this select? If it's a self join, that's fine. Just trying to understand what you want. – SS_DBA Mar 12 '19 at 16:09
  • Error Number: 1066 Not unique table/alias: 'interest' create temporary table temp as (select interest.staff_staffID, interestID, COUNT(industry) AS TotalFrequency from interest, interest where interest.staff_staffID = interest.interestID group by interest.staff_staffID) – Petru Maika Mar 12 '19 at 16:24
  • You need to group by interestID as well, since it's in the SELECT. – Chris Hackett Mar 12 '19 at 16:40
  • i have added a diagram of tables: https://i.stack.imgur.com/gDdN5.png – Petru Maika Mar 14 '19 at 16:11

1 Answers1

1

I would recommend writing the query like this:

create temporary table temp as
    select c.industry, count(*) AS TotalFrequency
    from interest i JOIN
         customers c
         on i.staff_staffID = c.interestID
    group by c.industry;

This is just a guess. You haven't provided table layouts. The join conditions are quite strange (but wouldn't result in an error, just non-matches).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786