If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > SQL Query for one field with multiple values

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
SQL Query for one field with multiple values-rel3.gif  
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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)
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On