I think my Access is broked vs. SQL query

Discussion in 'OT Technology' started by elevator, May 3, 2007.

  1. elevator

    elevator The tenants think it's wonderful! ヽ(´ OT Supporter

    Joined:
    Mar 25, 2005
    Messages:
    10,967
    Likes Received:
    0
    Location:
    Houston, TX
    My tables:
    [​IMG]

    My code:
    Code:
    SELECT DISTINCT Name, Salary
    FROM S, O
    WHERE S.Name = O.SalespersonName AND O.CustName NOT IN ('Abernathy Construction')
    ORDER BY S.Salary;
    I get:
    [​IMG]

    I need the result to be DISTINCT names and salaries of all salespeople who don't have an account with Abernathy Construction. Abel is right, but Jones shouldn't be there. Why is Jones there???? ty OT
     
  2. Create

    Create :free at last:

    Joined:
    Jan 4, 2006
    Messages:
    8,043
    Likes Received:
    2
    Jones has an accoutn with Abernathy, but also with Manchester. Abel has no accounts with Abernathy.

    The Jones-Manchester record is making it to the results.
     
  3. Create

    Create :free at last:

    Joined:
    Jan 4, 2006
    Messages:
    8,043
    Likes Received:
    2
    I'd be interested to know how to do this with a single statement, rather than two.
     
  4. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Code:
    SELECT DISTINCT Name, Salary
    FROM S, O
    INNER JOIN S on O.SalespersonName = S.name
    WHERE AND O.CustName NOT IN ('Abernathy Construction')
    ORDER BY S.Salary;
    totally off the top of my head. probably wont work.
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    why are you not using primary and foreign keys?
     
  6. elevator

    elevator The tenants think it's wonderful! ヽ(´ OT Supporter

    Joined:
    Mar 25, 2005
    Messages:
    10,967
    Likes Received:
    0
    Location:
    Houston, TX
    The database was given to me, I just have to do the queries.

    here is the correct statement btw:
    Code:
     SELECT DISTINCT Name, Salary
     FROM S, O
     WHERE S.Name = O.SalespersonName AND O.SalespersonName NOT IN (SELECT Otemp.SalespersonName FROM O Otemp WHERE Otemp.CustName IN ('Abernathy Construction'))
     ORDER BY S.Salary
     

Share This Page