sql - Postgresql: Query using multiple attributes

Question

I'm trying to create a query with Postgresql. Unfortunately, the attributes in the attribute_table are listed as rows instead of columns which makes it harder to pull. I want to pull a count based on the three attributes I have listed below (1000 = gender - 2 = female, 1001 = age group - 5 = 55-64, 1002 = household size = 1). How do I adjust this query so that it only gives me one row vs three rows of the same personal_ID? Also when I use this query, it doesn't pull any values but if I put only 1 attribute it works.

select sa.country_id ,count(distinct sa.personal_id ) 
from study_table sa ,attribute_table a 
where sa.country_id =a.country_id and sa.personal_id =a.personal_id 
and to_char(sa.mailing_date,'yyyy-MM')='2021-01' 
and attribute_id =1000  and a.attribute_number =2 
and attribute_id =1001  and a.attribute_number =5
and attribute_id =1002  and a.attribute_number =1 
and study_type ='Wave' 
and status not in ('NEW','EXCLUDED','ERROR') 
group by sa.country_id

question from:https://stackoverflow.com/questions/66066555/postgresql-query-using-multiple-attributes

Answer 1

First, use proper, explicit, standard JOIN syntax.

Second, your WHERE conditions are contradictory. You need ORs . . . and then a HAVING for the final filtering:

select sa.country_id ,count(distinct sa.personal_id ) 
from study_table sa join
     attribute_table a 
     on sa.country_id = a.country_id and
        sa.personal_id = a.personal_id 
where to_char(sa.mailing_date,'yyyy-MM') = '2021-01' and
      ( (attribute_id = 1000 and a.attribute_number = 2) or
        (attribute_id = 1001 and a.attribute_number = 5) or
        (attribute_id = 1002 and a.attribute_number = 1) 
      ) and
      study_type ='Wave' 
      status not in ('NEW','EXCLUDED','ERROR') 
group by sa.country_id
having count(distinct attribute_id) = 3;