Infrastructure Software

Hi, Björn,

In your second query, you use IN twice. The second time, the argument is a comma-delimited list, but you can use a sub-query just like you did in the first IN:

SELECT * FROM SALESMEETING_CUSTOMERLIST 
WHERE SALESMEETINGID IN
    (   -- Begin sub-query of salesmeetingid's wanted
    SELECT  SALESMEETINGID 
    FROM    SALESMEETING_CUSTOMERLIST 
    WHERE   CUSTOMERID IN
        (   -- Begin IN subquery of customerid's wanted
        SELECT    CUSTOMERID
        FROM      SALESMEETING_CUSTOMERLIST
        GROUP BY  CUSTOMERID
        HAVING    COUNT(*) > 1
        )   -- End sub-query of customerid's wanted
    )   -- End sub-query of salesmeetingid's wanted
;

create table SALESMEETING_CUSTOMERLIST(SALESMEETINGID,CUSTOMERID) as
select 10000,20001010 from dual union
select 10001,20002020 from dual union
select 10001,20001010 from dual union
select 10001,20007070 from dual union
select 10002,20003030 from dual union
select 10003,20001010 from dual union
select 10004,20002020 from dual union
select 10004,20005050 from dual union
select 10005,20006060 from dual;

select SALESMEETINGID,CUSTOMERID
from (select SALESMEETINGID,CUSTOMERID,
      max(cnt) over(partition by SALESMEETINGID) as maxCnt
      from (select SALESMEETINGID,CUSTOMERID,
            count(*) over(partition by CUSTOMERID) as cnt
              from SALESMEETING_CUSTOMERLIST))
where maxCnt >1;

SALESMEETINGID  CUSTOMERID
--------------  ----------
         10000    20001010
         10001    20001010
         10001    20002020
         10001    20007070
         10003    20001010
         10004    20005050
         10004    20002020

Hi Frank,

Thanks, I figured it was something like that but just couldn't get it to work =)

Again, thank you!

/Björn