Sql query to not return the user who has a particular value in an column of a different table

Question

So I am facing some problem with this sql query(MYSQL) where I do not want to return the user record if one of the columns in another table contains a certain value.

This the structure of the tables:

Description of the tables:

1. The person table is self explanatory. It just contains various info regarding the person that I am returning

2. The Person Attribute table describes the attributes a person may have along with its values and its type where person_attribute_type_id is a foreign key

3. The Person Attribute Type describes the different attributes types.

Now I have a person_attribute_type called PATIENT_STATE i.e. there is a record with name column in person_attribute_type table with value PATIENT_STATE

I do not want to return any persons, who have have a Person Attribute of type PATIENT_STATE, but all others.

The following is the sql query that I have written:

    select 
       p.uuid as uuid, pat.value as Atribute, p.person_id as personId,
       p.gender as gender, p.birthdate as birthDate, p.death_date as 
       deathDate, p.birthdate_estimated as birthdateEstimated,
       p.date_created as dateCreated
    from person p 

       left join person_name pn on pn.person_id = p.person_id
       JOIN person_attribute pat on p.person_id=pat.person_id and 
       pat.voided = 'false' and pat.person_attribute_type_id NOT IN 
        (SELECT person_attribute_type_id from person_attribute_type 
        where name='PATIENT_STATE') 
    where p.voided = 'false' and pn.voided = 'false' and ( 
    concat_ws(' ',coalesce(given_name), coalesce(middle_name), 
    coalesce(family_name)) like  '%Aud%') group by  p.person_id;

In the sql query %Aud% is like my search query

If I run the above query, it does not return the record for which the person had PATIENT_STATE but it returns a record where say the Person Attribute is XXX. The thing is that I want to check if the person contains PATIENT_STATE as his attribute. If there is, I do not want to return that person's record even if it contains any other attribute types.

How do I go about this?

Answer 1

I do not want to return any persons, who have have a Person Attribute of type PATIENT_STATE, but all others.

From what you describe, this sounds like a not exists query:

select p.*
from person p
where not exists (select 1
                  from person_attribute pa join
                       person_attribute_type pat
                       on pat.person_attribute_type_id = pa.person_attribute_type_id
                  where pat.name = 'PATIENT_STATE' and
                        pa.person_id = p.person_id
                 );

You can also express this as left joins if you want:

select p.*
from person p left join
     person_attribute pa
     on pa.person_id = p.person_id left join
     person_attribute_type pat
     on pat.person_attribute_type_id = pa.person_attribute_type_id and
        pat.name = 'PATIENT_STATE' 
where pat.person_attribute_type_id is null;

You query is returning a value from person_attribute_type. I don't think that makes sense, given that your question is for an attribute that is missing.