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



  • 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. enter image description here



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2