PostgreSQL select self referencing with two columns

Question

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

Answer 1

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