Skip to content
Advertisement

MYSQL joins and then making queries on the joined table

I am trying to do a join on a database and subsequent queries, in this database which is a dummy one from MYSQL y hace these tables

 +----------------------+
    | Tables_in_employees  |
    +----------------------+
    | current_dept_emp     |
    | departments          |
    | dept_emp             |
    | dept_emp_latest_date |
    | dept_manager         |
    | employees            |
    | salaries             |
    | titles               |
    +----------------------

and in the salaries table i have something like these

| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |

So I am trying to sum the salary and group the result by employee number so I have just one value per number, so That row could be used for a join with the employees table so I can do a query with the joined table, nevertheless I cannot do the join, I am trying to do something like these:

SELECT salary, salaries.emp_no FROM salaries INNER JOIN employees ON salaries.emp_no = employees.emp_no -----> This ONE JUST THROWS THE FIREST TWO COLUMNS FROM SALARIES


 SELECT emp_no, SUM(salary) as total_pay FROM salaries GROUP BY emp_no INNER JOIN employees ON employees.emp_no = salaries.emp_no; ------> THIS ONE TELLS ME I HAQVE A SYNTAX ERROR

so chat can i do to figure out this task? THANKS

Advertisement

Answer

Assuming you just want the total salaries by employee, you don’t even need the employees table:

SELECT s.emp_no, SUM(s.salary) as total_pay
FROM salaries s
GROUP BY s.emp_no ;

I would expect that you want it as of a certain date:

SELECT s.emp_no, SUM(s.salary) as total_pay
FROM salaries s
WHERE start_date <= '1990-01-01' and
      end_date > '1990-01-01'
GROUP BY s.emp_no ;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement