SELECT values where their foreign keys are duplicate

Question

Let's say that we have clients and providers. A client can have multiple providers (like the internet, phone, TV etc) and I would like to find clients' names who have multiple providers.

create table clients
(
    client_id char(8) not null,
    client_name varchar(80) not null,
    contract char(1) not null,
    primary key (client_id)
)

create table client_provider
(
    provider_id char(11) not null,
    client_id char(8) not null,
    primary key (provider_id, client_id),
    foreign key (provder_id) references providers ON DELETE CASCADE,
    foreign key (client_id) references clients ON DELETE CASCADE
);

Therefore, even without knowing anything about providers, we can know clients with multiple providers by the following relational algebra (just started learning, please correct me if I am wrong):

π client_name (

[ σ client_provider2.provider_id ≠ client_provider.provider_id ∧ client_provider2.client_id = client_provider.client_id (ρ client_provider2 (client_provider) ⨯ client_provider))

⨝ clients]

what I have tried so far (returning "not a GROUP BY expression" in line 1):

SQL> select c.client_name 
  2  from clients c
  3  inner join client_provider cp on c.client_id = cp.client_id
  4  group by cp.client_id
  5  having count(*) > 1;

user_13691634 · Answer

When using GROUP BY all columns used should either be in GROUP BY or in an aggregate function. To resolve the issue do the following:

Add cp.client_id in SELECT clause
Add c.client_name in GROUP BY clause

SELECT
    cp.client_id,
    c.client_name
FROM clients c
INNER JOIN client_provider cp
    ON c.client_id = cp.client_id
GROUP BY
    cp.client_id,
    c.client_name
HAVING
    COUNT(1) > 1

user_9097906 · Answer

All non-aggregated columns must be in group by clause, now you know that.

As you commented that you want to display only client_name but not client_id (while it has to be in the group by clause), use current query as source for the final result:

select client_name
from (-- current query begins here
      select cp.client_id,
             c.client_name
      from clients c join client_provider cp on c.client_id = cp.client_id
      group by cp.client_id, 
               c.client_name
      having count(*) > 1
      -- current query ends here
     );

Alternatively, you could do it by using (slightly modified) current query as a subquery:

select cl.client_name
from client cl
where cl.client_id in (select cp.client_id
                       from client_provider cp
                       group by cp.client_id
                       having count(*) > 1
                      );

SELECT values where their foreign keys are duplicate

2 Answers