Question 89297 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL select self referencing with two columns

I’m struggling with this for a while now and can’t seem to figure it out.

I have a clients, relations (which define relations and their type between clients) and consultations.
What I wan’t to do is count how many consultations of type new has been for partners. The problem is, it can happen that both clients have a consultation of type ‘New’ but then I only want to count one because I’m only interested in how many partners I have had in consultations.

Here’s a simplified sql of my database

CREATE TABLE clients (
  id INT NOT NULL,
  name VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE relations (
  id INT NOT NULL,
  from_client_id INT NOT NULL,
  to_client_id INT NOT NULL,
  relation_type VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE consultations (
  id INT NOT NULL,
  client_id INT NOT NULL,
  type_of VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

some seed data

insert into clients (id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Fiddle Blaab'), 
(4, 'Agnes Blaab'),
(5, 'Kid Blaab')

insert into relations (id, from_client_id, to_client_id, relation_type)
VALUES
(1, 1, 2, 'partner'),
(2, 3, 4, 'partner'),
(3, 3, 5, 'child'),
(4, 4, 5, 'child')

insert into consultations (id, client_id, type_of)
VALUES
(1, 1, 'New'),
(2, 2, 'New'),
(3, 2, 'Follow Up'),
(4, 3, 'New'),
(5, 4, 'Follow Up')

and selecting the data like this gives me a count of 3 but I would want in this case to get only 2.

SELECT 
count(*)
FROM consultations
INNER JOIN clients ON consultations.client_id = clients.id 
  AND "consultations"."type_of" = 'New'
INNER JOIN relations ON (relations.from_client_id = consultations.client_id OR relations.to_client_id = consultations.client_id)
  AND relations.relation_type = 'partner'

This is because both John Doe and Jane Doe which are "Partners" have a consultation of type "new", but in this case I just want to count one consultation since I’m only interested in how many partners where in a consultation, this select counts me both Jane and John as Partner.

So my expected result would be 1 for John Doe and Jane Doe, and a second one for Fiddle Blaab and Agnes Blaab.

I hope it makes sense what I’m trying to accomplish.

I’ve created a db fiddle

Tags:

2

Answers


  1. 0 
    select count(distinct r.id) from
(select id,from_client_id client_id from relations where relation_type='partner'
  union all
select id,to_client_id client_id from relations where relation_type='partner')r
inner join consultations c on r.client_id=c.client_id and c.type_of = 'New'
    count
    2

    fiddle

    Login or Signup to reply.
  2. 0

    As I understand your question, you want to count how many partner relations have a new consultation.

    This suggests a select on the relations with a subquery that filters based on consultations:

    select count(*) as cnt
from relations r
where r.relation_type = 'partner'
  and exists (
        select 1
        from consultations c
        where c.type_of = 'New' 
          and c.client_id in (r.from_client_id, r.to_client_id) 
    )

    We could also join and use distinct:

    select count(distinct r.id) as cnt
from relations r
inner join consultations c
    on c.client_id in (r.from_client_id, r.to_client_id)
where c.type_of = 'New' 
  and r.relation_type = 'partner'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search