Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Question SQL Query for one field with multiple values

    Hello i have created database of Employees and their rights to access many areas, using MS Access(see the attached image of my database schema)

    now, lets say i want to select ALL EmployeeIDs from r_rights table, who have access to areas 1 OR 5,
    here is my SQL code:
    SELECT r_rights.EmployeeID, r_rights.AreaID
    FROM r_rights
    WHERE r_rights.AreaID = 1 OR r_rights.AreaID = 5;


    it returns all EmployeeIDs who has access to area1 or area5

    But what if i want to list ONLY EmployeeIDs who have access to area1 AND area5?

    i tried to put this line in the above code:
    WHERE r_rights.AreaID = 1 AND r_rights.AreaID = 5;

    but it didnt work...no errors, but it didnt give any results


    similiar problem comes out when i want to select, for example all employees whoose card expires on 5th month of 2008 year... here is the SQL for it:

    SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, d_divisions.DivisionName, t_titles.TitleName, e_employees.CardExpireDate
    FROM t_titles INNER JOIN (d_divisions INNER JOIN e_employees ON d_divisions.DivisionID = e_employees.DivisionID) ON t_titles.TitleID = e_employees.TitleID
    WHERE (Year(CardExpireDate)) = 2008 AND (Month(CardExpireDate)) = 5;

    no errors, but it doesnt show any record...

    please help me to solve these problems, thank you
    Attached Thumbnails Attached Thumbnails rel3.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TeslaShock
    But what if i want to list ONLY EmployeeIDs who have access to area1 AND area5?
    since those would have to be separate rows, you are basically looking for an employee who has certain rows within his set of rows

    when you deal with a set of rows, GROUP BY should immediately come to mind

    the technique for this type of problem is to count the rows which satisfy the criteria, and then see if you got all the rows which you wanted...
    Code:
    SELECT r_rights.EmployeeID
      FROM r_rights
     WHERE r_rights.AreaID IN ( 1, 5 )
    GROUP
        BY r_rights.EmployeeID
    HAVING count(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    Hey thanks! it did work for one table query! really thanks!

    but no i am trying to modify my query as before, and show also firstname, lastname,cardexpire dat, for similiar query.

    i did combine my query and your suggestion like this:

    SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, d_divisions.DivisionName, t_titles.TitleName, e_employees.CardExpireDate
    FROM t_titles INNER JOIN ((d_divisions INNER JOIN e_employees ON d_divisions.DivisionID = e_employees.DivisionID) INNER JOIN (a_areas INNER JOIN r_rights ON a_areas.AreaID = r_rights.AreaID) ON e_employees.EmployeeID = r_rights.EmployeeID) ON t_titles.TitleID = e_employees.TitleID
    WHERE r_rights.AreaID IN ( 1, 5 )
    GROUP
    BY e_employees.EmployeeID
    HAVING count(*) = 2;


    but it showed the following error: You tried to execute a query that does not include the specified expression 'FirstName' as part of aggregate function.

    is this error because such method works only when i select from ONE table?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    Code:
    SELECT r_rights.EmployeeID
      FROM r_rights
     WHERE r_rights.AreaID IN ( 1, 5 )
    GROUP
        BY r_rights.EmployeeID
    HAVING count(*) = 2
    This approach will only work if the combination of (EmployeeId, AreaId) is unique. As soon as you have two rows with the same combination, the query may not return the expected result. What I would do instead is this:
    Code:
    SELECT t1.*
    FROM   r_rights AS t1 JOIN r_rights AS t2 ON t1.employeeid = t2.employeeid
    WHERE  t1.areaid = 1 AND
           t2.areaid = 5
    You also avoid complex GROUP BY conditions if you want to return a lot of columns in the select list.

    Note, however, that this has a distinct problem if you have many AreaId values that should be checked because each additional ID implies another join. Grouping is much more elegant in those cases.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stolze
    This approach will only work if the combination of (EmployeeId, AreaId) is unique.
    which, indeed, it is, by virtue of being the PK




    Quote Originally Posted by stolze
    ...because each additional ID implies another join. Grouping is much more elegant in those cases.
    that's my feeling, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Posts
    43
    Thanks friends!
    Now i need to run a query which shows FirstName,LastName,FathersName, and also shows passgiver and birthplace! by the DivisionID number

    here is my SQL code for it:

    SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, b_birthplaces.BirthPlaceName, p_passgivers.PassGiverName
    FROM b_birthplaces INNER JOIN (p_passgivers INNER JOIN (d_divisions INNER JOIN e_employees ON d_divisions.DivisionID = e_employees.DivisionID) ON p_passgivers.PassGiverID = e_employees.PassGiverID) ON b_birthplaces.BirthPlaceID = e_employees.BirthPlaceID
    WHERE d_divisions.DivisionID = 1;


    it works good, but problem is that some of my employees dont have passgivers...this field is empty, some of them have empty field of birthplaceID... but in my query i want to see everyone! (if field is empty, i want to see empty field in my query table)

    is this possible? if yes then how should i change my SQL code? what to add?

    thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT e_employees.FirstName
         , e_employees.LastName
         , e_employees.FathersName
         , b_birthplaces.BirthPlaceName
         , p_passgivers.PassGiverName
      FROM e_employees
    LEFT OUTER
      JOIN b_birthplaces 
        ON b_birthplaces.BirthPlaceID = e_employees.BirthPlaceID
    LEFT OUTER
      JOIN p_passgivers 
        ON p_passgivers.PassGiverID = e_employees.PassGiverID
     WHERE e_employees.DivisionID = 1
    removed the d table because it was performing no useful purpose in this query

    removed the unnecessary parentheses (note: if you really want them, then perhaps we should move this thread out of the ANSI SQL forum and into the Microsoft Access forum)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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