Hey guys,
    Let me get right down to it, I work for an agency that is regulated by HIPAA and to come into compliance with those standards I had to rewrite the security system that we use on our intranet. Well this is where the problem arises, HIPPA states that an employee may only view Medical Records of clients when it is required to perform their daily job functions.

Here is the problem: only certain people have the rights to view ALL of the client information all other employees are permitted to view clients that only belong to their service area (e.g. Substance Abuse) but clients may also belong to more then one service area (substance abuse and young adult)

When X user preforms a search I pass in as a parameter that users domain name and have a view setup of all the service areas they belong to; my actual search is preformed against a view that has every client and their corrosponding service area and my where statement is WHERE serv_fk IN (Sub Query users service areas).

The problem is with the client Views, it is a SELECT DISTINCT but when I Left Join my look up table of service areas I have duplicate records in my view because while Jon Doe is a unique client his record is different in his service area column. So when a user searches for jon doe and they belong to both service areas they will see both records, is there any way that I can search the aforementioned view but only return one client if they exsist more than once?

"The one language all programmers understand is profanity."

__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================

I am confused. Does the service area relate to the user or to the patient? Maybe you need an inner join rather than a left join?

I just resolved this. I place my search results in a temp table and was selecting all from that temp table changing this to SELECT DISTINCT fixed the problem. (The view contains duplicate yet unique values only because of the program service column, when I place a search result in my temp table, i dont place the service area into that table so DISTINCT returns exactly what i need)

"The one language all programmers understand is profanity."