Clash Royale CLAN TAG#URR8PPP

How to write a SQL query to get the below Scenario output

I have 2 table like below and I need the client_ID from client table and his Roles from roles table, but the condition to get the data the tables is, client whose is having more than one contribution_type.

Please find the below sample table and output.

Client table

Client_ID cname Contribution_type
-------------------------------------
1 A Regular
2 B public
3 C regular
4 D private
1 A public
4 D similar


Role table

Client_ID Rname
------------------
1 owner
2 owner
3 trustee
4 benificier
1 trustee
2 benificier
3 owner
4 owner


Output

Client_ID Rname
-------------------
1 owner
1 trustee
4 beneficiary
4 owner


I've written the below query but getting the below error

select
c.cid, r.rname
from
Client_table c
join
role_table r on c.cid = r.CID
where
c.cid in (select cl.CID, count(Contribution_type)
from Client_table c
group by cl.CID
having count(Contribution_type) > 1);


Error message:

ORA-00913: too many values

00913. 00000 - "too many values"

*Cause:

*Action:

Error at Line: 21 Column: 9

Thanks in advance.

5 Answers
5

Try to remove count(Contribution_type) in your where subquery. because In only can compare one column.

count(Contribution_type)

where

select c.cid,r.rname
from Client_table c
join role_table r on c.cid = r.CID
where c.cid in (select cl.CID from Client_table c group by cl.CID having count(Contribution_type)>1);


or you can just write a subquery in from instead of in where.

from

where

select c.cid,r.rname
from (
select cl.CID
from Client_table c
group by cl.CID
having count(c.Contribution_type)>1
) c
join role_table r on c.cid = r.CID


This will do it for you.
Assuming that in case of same contribution type appearing more than once, you want only one instance, use count (distinct contribution_type) else just use count(contribution_type)

contribution type

count (distinct contribution_type)

count(contribution_type)

select * from role r
where r.client_id in
(select client_id from client c
group by client_id
having count(distinct contribution_type)>1
)


The reason for the error is that in your IN clause, you have mentioned only 1 value, but you are fetching more than 1 values from your subquery, hence the error.

IN

select c.cid,r.rname from Client_table c join role_table r on c.cid = r.CID
where c.cid in (select cl.CID from Client_table c group by cl.CID having count(Contribution_type)>1);


Remove count(Contribution_type) in your subquery.

it will do :

select distinct client_id,rname from role_table where client_id in(1,4) order by client_id;


Your problem is that the subquery is returning multiple columns. But, you can also simplify the outer query:

select r.cid, r.rname
from role_table r
where r.cid in (select c.CID -- only one column here
from Client_table c
group by c.CID
having count(c.Contribution_type) > 1
);


In other words, you don't need the join to Client_table because you have all the fields you need in role_table.

Client_table

role_table

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.