Want to use IF THEN in SQL

Question

I need to ID/tag all contact records of our active clients, even if only ONE of the client's contact records is tagged as ACTIVE.

Specifically, I want to pull all contact records from all companies in my GoldMine database in which ANY ONE of the company's contact records has the company's account status tagged as "ACTIVE CLIENT". Then I plan to take the SQL output, create a group, taking all the contact records from this list and replace the company's account status field from "blank or null" to "ACTIVE CLIENT".

Thanks for any help. -- Jeannie

Answer 1

Are you saying you want something that amounts to something like this?

UPDATE Company
SET active = 1
WHERE CompanyID IN (
    SELECT
        Client_CompanyID
    FROM
        Contacts          -- or do you mean clients?
    WHERE
        IsActive = 1
)

?

If not, pleas let us know what, other than that, you want.

And please, oh please, don't say "and this has to work in MySQL 3.x or 4.x."

Answer 2

Okay, it sounds like you have a list of active clients (companies) and in addition you have a list of all the contacts (people) for a particular client (company).

It seems that you want to take all contacts (people) for a particular client (company) and update an account status column to "active client" for all of those contacts (people) for that particular client (company)- so long as at least one of the contacts (people) for that client (company) has its account status column as "active client".

if these are the correct requirements, then you could do this:

UPDATE Contacts

SET Account_Status = 'ACTIVE CLIENT'

WHERE Client_ID IN (SELECT Client_ID
    FROM Contacts
    WHERE Account_Status = 'ACTIVE CLIENT')