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

    Unanswered: Many-to-many table and WHERE clause with more than one values

    Hello,

    first please take a look at the attached schema of my database.

    Next, i want to list ALL the Employees from my database, who have access to the AreaID,specified in the MessageBox, here is my SQL code for this query:
    SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, d_divisions.DivisionName, t_titles.TitleName
    FROM t_titles INNER JOIN ((((d_divisions INNER JOIN d_departments ON d_divisions.DivisionID = d_departments.DivisionID) INNER JOIN e_employees ON d_departments.DepartmentID = e_employees.DepartmentID) INNER JOIN c_cards ON e_employees.EmployeeID = c_cards.EmployeeID) INNER JOIN r_rights ON c_cards.CardID = r_rights.CardID) ON t_titles.TitleID = e_employees.TitleID
    WHERE r_rights.AreaID = ?;


    it works good as i see...

    problem is, what if i want to list all the employees who have access to the Area 5 and 7. or...say...3,4,7 and 12

    i tried this at the end of my first SQL code:
    WHERE r_rights.AreaID = 5 AND r_rights.AreaID = 7;

    but it didnt work...no errors, but it returned no records (there are such records)

    please could you guide me how to do it?

    thanks
    Attached Thumbnails Attached Thumbnails rel3.gif  

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Try:
    WHERE r_rights.AreaID = 5 OR r_rights.AreaID = 7;
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    i tried this, it returns any Employee who has access to area5 or area7, but what if i want to list ONLY employees who have access to area5 AND area7?

    and i have remaked my database design, please take a look at my attachment.

    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  
    Last edited by TeslaShock; 11-25-07 at 07:29.

  4. #4
    Join Date
    Nov 2007
    Posts
    11

    WHERE r_rights.AreaID = 5 AND r_rights.AreaID = 7

    Because an SQL query pulls out only one record at a time, the statement WHERE r_rights.AreaID = 5 AND r_rights.AreaID = 7 for the same field is not going to work.

    There's always more than one way to something. I always need to have the data at my hands to offer a specific solution. My suggestion is to first setup a subquery that finds all the employees that have card access to the areaID (7).

    This subquery should only return a single column (employeeID).

    Then set up the main query that searches out on the first value, similarily as you have it in your example. Query statement to get all employees having access to areaID 5 and then add "WHERE e_employees.employeeID IN" and then add your subquery (SELECT statement to get employeeID where AreaID = 7)

    This searches out only the employees having access to area 5 who also have access to area 7.

    This can be nested again should you want to do that for 3 values. Each subquery can only return one column. At least in my experience.

    This is one idea, hope this helps.

Posting Permissions

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