Omit a member/user row if it meets one of the value - AD HOC Query

Question

I have searched all over in google and I don't think I'm using the right keyword to get the result I'm looking for. If this question has already been asked, please re-direct me to the question. If not, I hope you'll be able to understand what I'm looking for.

A quick background: I'm a newbie in SQL language and I've been teaching myself for a while. I work for a mental health organization and they are using a Credible electronic health record. It has a report tab where I am able to build an export report using Credible's "Query Builder 2.0" and it is a Custom AD HOC. I've been able to build a report that shows me the list of new members with its primary center without any issue.

Now I'm working on building a report where it'll tell me which members only have a visit type called "No Contact". I want to skip the member that has other visit types including the "No Contact" and I didn't have any success yet. I use form_id to filter it out. '354' is a no contact visit type.

I don't need it to show the latest record.

[client_id] | [last_name] | [first_name] | [form_id]
[ 10001   ] | [   Doe  ]  | [  John    ] | [  354  ]
[ 10001   ] | [   Doe  ]  | [  John    ] | [  445  ]
[ 10001   ] | [   Doe  ]  | [  John    ] | [  431  ]
[ 10001   ] | [   Doe  ]  | [  John    ] | [  515  ]
[ 10002   ] | [ Ghost  ]  | [  Jane    ] | [  354  ]
[ 10002   ] | [ Ghost  ]  | [  Jane    ] | [  354  ]

The result I want to see is only Jane Ghost because the only visit type that has been entered in the entire record is "No Contact" I want it to be able to skip John Doe because John has more than 1 different visit type.

This is the code I typed so far, I just don't know which statement to use to be able to get the result I want. Is it the 'case' statement or the 'Group by' statement? that is what I've been struggling to search for. Maybe it is not even possible at all.

Select C.client_id,
  C.last_name,
  C.first_name,
  V.form_id as form_id
From Clients C
  Left Outer Join ClientVisit CV On C.client_id = CV.client_id
  Inner Join VisitType V On CV.visittype_id = V.visittype_id
  Inner Join Forms F On F.form_id = V.form_id
Order By C.last_name,
  C.first_name

Any guide would be greatly appreciated. If this already has been asked, I apologize in advance.

EDIT To add update Code

I almost got it, but it still shows other than 354 for the form ID number. I'm fine with null value, but I need it to not show other than 354 at all.

Select C.client_id,
  C.last_name,
  C.first_name,
  P.program_code As prim_program
From Clients C
  Inner Join ClientPrograms CP On C.client_id = CP.client_id
  Inner Join Programs P On CP.program_id = P.program_id
Where C.client_status = 'active' And CP.primary_flag = 'true'
Except
Select CV.client_id,
  CV.clientlastname,
  CV.clientfirstname,
  P.program_code
From ClientVisit CV
  Inner Join VisitType V On CV.visittype_id = V.visittype_id
  Inner Join ClientPrograms On CV.client_id = ClientPrograms.client_id
  Inner Join Programs P On P.program_id = ClientPrograms.program_id
Where V.form_id <> 354
Order By prim_program,
  last_name,
  first_name Desc

Answer 1

You need to remove a person if they have ever had a visit type other than 'No Contact'? You will first need to construct a list of the user_id's that have only had 'No Contact' visit types, and then join that with the rest of your information.

One possible way is to:

1. Select all client_ids
2. From this query, exclude all client_ids that have a visittype that is not 'No Contact'.
3. Then join that result with the rest of your columns.

Something like:

SELECT client_id from Clients
EXCEPT
SELECT client_id from VisitType
WHERE form_id <> 354

This will select every client_id in Clients, and then remove every client row that has ever had a visit type that is not "No Contact". Note that this will still leave rows of client_ids who have never had a visit, because they don't have a non-"No Contact" visit. :D

(then join the result of the above with the rest of your tables/columns.)

Depending on the SQL you're using, the important operator may be either EXCEPT, or WHERE NOT EXISTS. Googling either of those may set you on the right track.

Alternately:

SELECT client_id from Clients
WHERE NOT EXISTS
SELECT client_id from VisitType
WHERE form_id <> 354

Do not do this:

SELECT client_id from Clients
WHERE EXISTS
SELECT client_id from VisitType
WHERE form_id = 354

This would erroneously select everyone who has ever had a "No Contact" appointment. It's equivalent to:

SELECT client_id from Clients
INNER JOIN VisitType
ON Clients.client_id = VisitType.client_id
WHERE VisitType.form_id = 354

(Which would also be wrong...) :D

Answer 2

Potential Solution to the Code. All thanks to @itnAAnti for giving me something to start with!

Select C.client_id,
  C.dob,
  C.last_name,
  C.first_name,
  Coalesce(C.date21, C.date14) As Start_Date,
  P.program_code As prim_program,
  C.home_phone,
  C.client_email
From Clients C
  Inner Join ClientPrograms CP On C.client_id = CP.client_id
  Inner Join Programs P On CP.program_id = P.program_id,
  VisitType V
Where Coalesce(C.date21, C.date14) Between Cast(@param1 As date) And
  Cast(@param2 As date) And C.client_status = 'active' And CP.primary_flag =
  'true'
Except
Select CV.client_id,
  C.dob,
  CV.clientlastname,
  CV.clientfirstname,
  Coalesce(C.date21, C.date14) As Start_Date,
  Programs.program_code,
  C.home_phone,
  C.client_email
From ClientVisit CV
  Inner Join VisitType V On CV.visittype_id = V.visittype_id
  Inner Join ClientPrograms On CV.client_id = ClientPrograms.client_id
  Inner Join Programs On Programs.program_id = ClientPrograms.program_id
  Inner Join Clients C On C.client_id = CV.client_id
Where V.form_id <> 354
Order By prim_program,
  last_name,
  first_name

Setting the date range for the member's start date makes a huge difference as far as I can tell.