7

I have following table:

create table test (
  company_id integer not null, 
  client_id integer not null, 
  client_status text,
  unique (company_id, client_id)
);

insert into test values
  (1, 1, 'y'),    -- company1

  (2, 2, null),   -- company2

  (3, 3, 'n'),    -- company3

  (4, 4, 'y'),    -- company4
  (4, 5, 'n'),

  (5, 6, null),   -- company5
  (5, 7, 'n')
;

Basically, there are 5 different companies, each one has one or more clients and each client has status: 'y' or 'n' (might be null as well).

What I have to do is to select all pairs (company_id, client_id) for all companies for which there is at least one client whose status is not 'n' ('y' or null). So for the example data above, the output should be:

company_id;client_id
1;1
2;2
4;4
4;5
5;6
5;7

I tried something with window functions but I can't figure out how to compare the number of ALL clients with the number of clients with STATUS = 'n'.

select company_id,
count(*) over (partition by company_id) as all_clients_count
from test
-- where all_clients_count != ... ?

I figured out how to do this, but I am not sure if it's the right way:

select sub.company_id, unnest(sub.client_ids)
from (
  select company_id, array_agg(client_id) as client_ids
  from test
  group by company_id
  having count(*) != count( (case when client_status = 'n' then 1 else null end) )
) sub
  • Your requirements are unclear. Do you need to select the companyids and clientids or just the companyids? – Andrew Brennan May 31 '16 at 12:58
  • You're right, sorry for that, I updated question (expected result). When at least one client in company has status null or status != 'n' then I want to get this company along with all clients, even with status = 'n'. In other words I want to filter out all companies (along with clients) for which all clients have status = 'n'. – user606521 May 31 '16 at 16:07
  • Important missing detail: Thee is no UNIQUE constraint on (company_id, client_id) in your table definition. But your test data make it seem like (company_id, client_id) is, in fact, unique. Is it? If not, do you need * all* qualifying rows, or each combination of company_id and client_id just once? – Erwin Brandstetter Jun 1 '16 at 1:42
  • Yes, you are right, (company_id, client_id) is unique. – user606521 Jun 1 '16 at 10:08
  • @ErwinBrandstetter sorry, I ended up using my (last one in question) solution because for now it works quite well for me. Anyway I will accept your answer as I am pretty sure it's correct and most complete :). – user606521 Jun 20 '16 at 12:19
5

Basically you are looking for the expression:

client_status IS DISTINCT FROM 'n'

The column client_status should really be data type boolean, not text, which would allow the simpler expression:

client_status IS NOT FALSE

The manual has details in the chapter Comparison Operators.


Assuming your actual table has a UNIQUE or PK constraint, we arrive at:

CREATE TABLE test (
  company_id    integer NOT NULL, 
  client_id     integer NOT NULL, 
  client_status boolean,
  PRIMARY KEY (company_id, client_id)
);

Queries

All of these do the same (what you asked), which is the fastest depends on data distribution:

SELECT company_id, client_id
FROM   test t
WHERE  EXISTS (
   SELECT 1 FROM test
   WHERE  company_id = t.company_id
   AND    client_status IS NOT FALSE
   );

Or:

SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT company_id
   FROM   test t
   GROUP  BY 1
   HAVING bool_or(client_status IS NOT FALSE)
   ) c USING (company_id);

Or:

SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT DISTINCT company_id, client_status 
   FROM   test t
   ORDER  BY company_id, client_status DESC
   ) c USING (company_id)
WHERE  c.client_status IS NOT FALSE;

Boolean values sort FALSE -> TRUE -> NULL in ascending sort order. So FALSE comes last in descending order. If there's is any other value available, then that one's picked first ...

The added PK is implemented with a useful index for these queries. If you want faster, yet, add a partial index for query 1:

CREATE INDEX test_special_idx ON test (company_id, client_id)
WHERE  client_status IS NOT FALSE;

You could use window functions, too, but that would be slower. Example with first_value():

SELECT company_id, client_id
FROM  (
   SELECT company_id, client_id
        , first_value(client_status) OVER (PARTITION BY company_id
                                           ORDER BY client_status DESC) AS stat
   FROM   test t
   ) sub
WHERE stat IS NOT FALSE;

For lots of rows per company_id, one of these techniques may be faster, still:

2

I may have misunderstood you but I imagine something like:

 select * 
 from test x 
 where exists ( 
     select 1 
     from test y 
     where x.company_id = y.company_id 
       and coalesce(client_status, 'y') <> 'n'
 );

will work. coalesce is use to map null to 'y', but anything different than 'n' should do

Using an OLAP function can save us a "join":

select company_id, client_id 
from (
    select x.*
         , count(nullif(coalesce(client_status,'y'),'n')) 
               over (partition by company_id) as cnt 
    from test x
) 
where cnt > 0;

Here we map null -> 'y' and 'n' -> null. Since count(x) will count rows where x is not null, we count rows where client_status <> 'n'. I used an OLAP function to avoid GROUP BY, which means that we only need to reference the table once.

1

I think this can be simplified a bit:

select company_id 
from test 
group by company_id 
having count(*) filter (where client_status!='n' or client_status is null) > 0;
0

A standard SQL query below should work

select
  company_id,
  client_id
from test
where client_status!='n' or client_status is null;
  • Sorry, my fault, I did not write question (expected result) precisely: when at least one client in company has status null or status != 'n' then I want to get this company along with all clients, even with status = 'n'. – user606521 May 31 '16 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.