Create a select query to get employee name, total salary of employee, hobby name(comma-separated - you need to use subquery for hobby name)

Question

see the below query table.

Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby). hobby: id, name employee: id, first_name, last_name, age, mobile_number, address employee_salary: id, foreign key of employee, salary employee_hobby: id, foreign key of the employee, foreign key of hobby

I execute the following query

SELECT CONCAT(e.first_name, ' ', e.last_name) AS full_name
     , SUM(es.salary) AS total_salary
     , (SELECT GROUP_CONCAT(h.name) 
        FROM hobby h 
        INNER JOIN hobby 
            ON h.id = eh.fk_hobby_id
       ) AS hobby_name 
FROM employee_hobby eh
INNER JOIN employee e 
    ON e.id = eh.fk_employee_id
INNER JOIN employee_salary es 
    ON es.fk_employee_id = eh.fk_employee_id
GROUP BY eh.fk_employee_id;

but the in this query hobby_name raw gets multiple hobby_name names that are not defined in the hobby table. and I have to complete this task by using the subquery Here is my OUTPUT Image.

Answer 1

The lack of ddl and sample data makes it difficult to guess where the problem might be, but the following looks very suspicious:

INNER JOIN hobby 
    ON h.id = eh.fk_hobby_id

It means that you will multiply the number of hobbies for each employee with the cardinality of the hobby table. You may want to try:

SELECT CONCAT(e.first_name, ' ', e.last_name) AS full_name
     , SUM(es.salary) AS total_salary
     , (SELECT GROUP_CONCAT(h.name) 
        FROM hobby h 
        WHERE h.id = eh.fk_hobby_id
       ) AS hobby_name 
FROM employee_hobby eh
INNER JOIN employee e 
    ON e.id = eh.fk_employee_id
INNER JOIN employee_salary es 
    ON es.fk_employee_id = eh.fk_employee_id
GROUP BY eh.fk_employee_id;

Group by eh.fk_employee_id may or may not be correct, but it is hard to tell without ddl for the tables.