Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts

    Left join help needed

    I'm try to create a left join query that will show employees who are NOT assigned to a given department.

    I have an Employees table with the following cols:
    id
    first_name
    last_name

    I have an EmpDepts table with the following cols:
    id
    emp_id
    dept_id

    And finally, I have a Departments table with the following cols:
    id
    dept_name

    This allows me to associate more than one department with one employee simply by having as many EmpDepts records with the emp_id and dept_id cols set with the correct id's.

    I have a form where I can assign a dept to an employee. I have a list of employees on the left side, the departments list in the center and on the right, a list of employees assigned to the selected department.

    When I select a department from the center list (maintenance dept for example) the right list shows employees assigned to that dept and now I'm try to create a query that will show employees on the left side list who are NOT assigned to the department 'maintenance'.

    I have the following query but it is not working for the left side employees remaining that could be assigned to the target department. I see employees in this list that have already been assigned to the selected department

    PHP Code:
    SELECT E.idE.last_name FROM Employees E LEFT JOIN EmpDepts ED ON E.id=ED.emp_id AND ED.emp_id <> E.id 
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    I'm sorry, but that query makes no sense.
    Code:
    ON E.id=ED.emp_id AND ED.emp_id <> E.id
    ???? That is an *IMPOSSIBLE* condition. It will *ALWAYS* be FALSE.

    Because you are asking for ED.emp_id to *BOTH* be EQUAL *AND* NOT EQUAL to E.id.

    So there will never be any matches in the EmpDepts table.

    As to why you then get *ALL* employees:

    By definition, a LEFT JOIN will show you all the records in the left-side table. That is, in your query, all the records in the EMPLOYEES table.

    That's the whole reason to use a LEFT JOIN: You *WANT* all the records from the left side table, whether or not there is a matching record in the right side table.

    *THIS* is what you need, I think:
    Code:
    SELECT E.id, E.last_name 
    FROM Employees E LEFT JOIN EmpDepts ED 
    ON E.id = ED.emp_id 
    WHERE ED.emp_id IS NULL

  • #3
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts
    The problem is, the EmpDepts table may contain only one record (one employee assigned to one dept) but I want to display ALL the employees (from the left table) that isn't assigned to that one dept, but because I'm joining these two tables, I'm only getting the employee records that match the employees and empdept table ID's.

    I have to rethink this.
    Last edited by rfresh; 06-27-2011 at 05:25 AM.
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Okay, minor change:
    Code:
    SELECT E.id, E.last_name 
    FROM Employees E LEFT JOIN EmpDepts ED 
    ON E.id = ED.emp_id AND ED.dept_id = 17
    WHERE ED.emp_id IS NULL
    Where I'm guessing about the name of the field (dept_id) and giving you a dummy value (17).

    Adjust to fit.

  • Users who have thanked Old Pedant for this post:

    rfresh (06-27-2011)

  • #5
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts
    That worked! You're a genius!

    Thanks...
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com


  •  

    Posting Permissions

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