jhdalton Posted October 25, 2012 Share Posted October 25, 2012 I’m new to writing SQL, and most of my needs so far have been met in the wonderful w3 SQL tutorial. However, I have a desire I haven’t been able to find a way to do, and I can't find info on it in the forums either. I have 3 inner joined tables in the part of the code I’m struggling with. ClientsView EmployeeClients Employees For a given client (identified by ClientsView.client_id) there are many employees linked to the client. I want to search those employees and return in a single column “Primary_Staff” the employee assigned to the client who meets certain criteria. In all cases the field EmployeeClients.primary_flag will need to be “YES”. The kicker is that what I want it to look for is first to see if an employee is found where the Employee.profile_code = “MHP”. If so, that is the employee I want listed in the new “Primary_Staff” field. If no one is found with that profile code, then I want it to search the employees to see if any is found where Employee.profile_code = “On Call MHP”. Then Employee.profile_code = “LPE”. And a few others, but that’s enough for the example. If it goes through the list of profile codes I’m looking for and doesn’t find anyone, then I just want it to say “Unassigned.” Thanks in advance for the help, and let me know what other information is needed if you need more info to be able to give me some help. Link to comment Share on other sites More sharing options...
niche Posted October 25, 2012 Share Posted October 25, 2012 (edited) I thought you could only JOIN two tables at a time (haven't had to do three yet), but I easily found this link. I hope it helpshttp://forums.tutori...ables-4808.html I will follow this topic. Edited October 25, 2012 by niche Link to comment Share on other sites More sharing options...
jhdalton Posted October 25, 2012 Author Share Posted October 25, 2012 Thanks...my terminology may be incorrect, but the joining is not the problem I'm having, really. The problem I'm having is having a column that looks through the multiple employees who match the employee field, but do so in a specific way. To say it another way, I could easily setup a filter that would only show employees who have the profile code "MHP" - but if there isn't someone with the profile code "MHP" then I want it to put someone with the profile code "On Call MHP" and if there isn't anyone with that code, the person with the profile code "LPE" and if that doesn't exist, then put "unassigned" in that calculated/created column. Maybe this makes more sense explained this way? Link to comment Share on other sites More sharing options...
niche Posted October 25, 2012 Share Posted October 25, 2012 use the keyword OR Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now