CODEWITHSUNDEEP
sqloracle-databaseinner-joinhaving
spas2k
spas2k

Reputation: 519

Join and Having - get the accounts that appear more than once in the contract table

I'm trying to get the accounts that appear more than once in the contract table, regardless of the number of times it's joined in the c_map table.

CREATE TABLE cntrct (
  cntrct_id VARCHAR(10),
  account_id varchar(10)
);
CREATE TABLE c_map (
  cntrct_id VARCHAR(10)
);

INSERT INTO cntrct VALUES (1,'a');
insert into cntrct values (2,'b');
insert into cntrct values (3,'c');
insert into cntrct values (4,'b');

INSERT INTO c_map VALUES (1);
INSERT INTO c_map VALUES (1);
INSERT INTO c_map VALUES (1);
insert into c_map values (2);
insert into c_map values (2);
insert into c_map values (2);
insert into c_map values (3);
insert into c_map values (3);
insert into c_map values (3);
commit;

select ct.account_id
from cntrct ct, c_map cm
where ct.cntrct_id = cm.cntrct_id
group by ct.account_id
having count(ct.account_id)> 1

Fiddle: http://sqlfiddle.com/#!4/cec1b7/4

I'm expecting an output of:

 b

But instead I'm getting all of them.

How can I limit it so it doesn't take the c_map table into consideration when running the having count()>1?

Upvotes: -1

Views: 29

Answers (1)

GMB
GMB

Reputation: 222612

get the accounts that appear more than once in the contract table, regardless of the number of times it's joined in the c_map table.

Why join at all? The information you want is in the contract table:

select account_id
from cntrct 
group by account_id
having count(*) > 1

Maybe you want to filter the dataset on contract id that exists in the map table. If so, I would recommend exists:

select account_id
from cntrct c
where exists (select 1 from c_map m where m.cntrct_id = c.cntrct_id)
group by account_id
having count(*) > 1

Upvotes: 3

Related Questions