Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Sep 2007
    Thanked 0 Times in 0 Posts

    Need help with join

    I have a query that isn't working the way I think it should:

    PHP Code:
    SELECT E.last_name,
    COUNT(ED.id) AS dcnt FROM Employees E
    LEFT JOIN EmpDepts ED ON E
    .id ED.emp_id
    .company_index 63
    .last_name HAVING dcnt 
    My EmpDepts (employee departments) table structure looks like this:

    PHP Code:
     id      company_index   emp_id  dept_id
     882     63              1064    479
     883     63              1064    477 
    If an employee has been assigned to a Department, in the EmpDepts table, a record is created that shows the employee id and the associated department table id.

    If no department is assigned to an employee, there will be no record in the EmpDepts table for that employee record id.

    That top query that isn't working should be returning 6 records out of my 200 records because 6 are not assigned to any departments, but my query is returning only 2 records, thus, missing other 4 who have not been assigned to any departments.

    I've checked the record structure of the 2 records that are being 'caught' by the top query vs the 4 that are not being caught and I can't see any differences. I think the main thing about that query is that I've manually verified that they are not in the EmpDepts table...so I'm puzzled as to why the query doesn't catch all 6 records.

    Thanks for any help.

  2. #2
    Senior Coder
    Join Date
    Dec 2005
    Thanked 79 Times in 79 Posts
    this is what you want:
    6 are not assigned to any departments
    this is how to get it:
    SELECT E.last_name
    FROM Employees E
    LEFT JOIN EmpDepts ED ON E.id = ED.emp_id
    WHERE ED.emp_id is null and E.company_index = 63
    you don't need to count as count is 0 anyway.
    Last edited by BubikolRamios; 02-13-2013 at 11:55 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  3. Users who have thanked BubikolRamios for this post:

    frank727 (02-13-2013)

  4. #3
    New Coder
    Join Date
    Sep 2007
    Thanked 0 Times in 0 Posts


  5. #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    *** CAUTION ***

    That query works in this particular instance, but it will fail in most other situations.

    When you use a condition in the WHERE clause that refers to the *dependent* table in an outer join (that is, the RIGHT table in a LEFT JOIN, for example), you just converted that outer join into an INNER join!!!

    The sole exception to this rule is when the condition you are testing is IS NULL (or IS NOT NULL) as Bubikol is doing here.

    For other conditions, you *MUST* move the condition on the dependent table to the ON section of the query, thus (just an example, not applicable to this case):
    SELECT E.last_name
    FROM Employees E
    LEFT JOIN EmpDepts ED 
         ON E.id = ED.emp_id AND ED.dept_no <> 17
    WHERE E.company_index = 63
    If the part of the condition there in red were moved to the WHERE, you would no longer have a LEFT JOIN.

    Again, not relevant in this case, but a fair warning for the future.
    Be yourself. No one else is as qualified.


Posting Permissions

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