Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    17

    Unanswered: Code with 3 LEFT OUTER JOINS will not work, problem with GROUP BY/HAVING clause.

    Hi again,

    After such great help in my last post I thought I had definately got my head around why the code kept failing.

    However another part of the problem yields another set of error messages from SQL Developer

    So, again if anyone can shed any light on this situation I would be eternally grateful.

    Code:
    CREATE OR REPLACE VIEW TOP_EARNER_PER_LOCATION
    AS
    SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
               L.REGIONAL_GROUP AS REGIONAL_GROUP, 
               E.SALARY AS SALARY, 
               J.JOB_ID 
    FROM EMPLOYEE E
    LEFT OUTER JOIN DEPARTMENT D
    ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
    LEFT OUTER JOIN LOCATION L 
    ON L.LOCATION_ID = D.LOCATION_ID
    LEFT OUTER JOIN JOB J 
    ON J.JOB_ID = E.JOB_ID
    GROUP BY E.FIRST_NAME || ' ' || E.LAST_NAME
    HAVING J.JOB_ID NOT IN (670,671,672)
    ;
    
    REM #...SELECT STATEMENT TO RUN QUERY GOES HERE
    Many thanks in advance and apologies for this being my second question today

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This looks wrong; you can't GROUP BY only those two columns - you need to include the rest of them (non-aggregate ones from the SELECT statement) as well. It seems that something like this might be OK:
    Code:
    CREATE OR REPLACE VIEW top_earner_per_location
    AS
       SELECT e.first_name || ' ' || e.last_name AS employee_name,
              l.regional_group AS regional_group,
              e.salary AS salary,
              j.job_id
         FROM employee e
              LEFT OUTER JOIN department d
                 ON d.department_id = e.department_id
              LEFT OUTER JOIN location l
                 ON l.location_id = d.location_id
              LEFT OUTER JOIN job j
                 ON j.job_id = e.job_id
        WHERE j.job_id NOT IN (670, 671, 672);

  3. #3
    Join Date
    Jun 2011
    Posts
    17
    Hi,

    I managed to get my code working after sorting out the group by part.

    However it brings through the following data:

    Code:
    EMPLOYEE_NAME                   REGIONAL_GROUP       SALARY                 
    ------------------------------- -------------------- ---------------------- 
    FRED JAMES                      CHICAGO              950                    
    DENISE SOMMERS                  CHICAGO              1850                   
    JAMES MURRAY                    DALLAS               750                    
    JOHN SMITH                      DALLAS               800                    
    DIANE ADAMS                     DALLAS               1100                   
    RICHARD LEWIS                   DALLAS               1800                   
    DONALD SCOTT                    DALLAS               3000                   
    JENNIFER FORD                   DALLAS               3000                   
    ALICE JENSEN                    NEW YORK             750                    
    MICHAEL DOUGLAS                 NEW YORK             800                    
    BARBARA MILLER                  NEW YORK             1300                   
    GRACE ROBERTS                   NEW YORK             2875                   
    MATTHEW FISHER                  NEW YORK             3000
    However it is supposed to be bringing the HIGHEST earner per location not just the salary and location.

    Hope that makes sense,

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by u.c.dispatj View Post
    it is supposed to be bringing the HIGHEST earner per location not just the salary and location.
    There is absolutely nothing in your query that would suggest it. I would expect a WHERE clause...

    SQL would be loosely based on how the problem is stated in human language.

    "SHOW employee FROM among all employees, WHOSE salary IS the MAXIMUM salary of employees AT THAT location"

    "SELECT name FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees WHERE location = e1.location)"

    I'm sure you will be able to add joins where necessary.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •