2

Sorry for the title I just do not know how to explain what I want to achieve briefly. But it is actually simple.

I have the following table egr:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
+---------+------------+

I would like to get the rows for which a groupid is not linked to the other offid. Result would be:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       2 | 404        |
+---------+------------+

This works but I was wondering if there was a more elegant way to do it?

select * from egr as egr1
where egr1.offid = 1
and egr1.groupid not in (select groupid from egr as egr2 where egr2.offid = 2 and egr1.groupid = egr2.groupid)
union
select * from egr as egr1
where egr1.offid = 2
and egr1.groupid not in (select groupid from egr as egr2 where egr2.offid = 1 and egr1.groupid = egr2.groupid)

In case you would like to try:

create table egr (offid int, groupid int);
insert into egr values (1, 101), (1, 202), (2, 202), (2, 404);

Thanks

Stephane B.
  • 542
  • 6
  • 18

3 Answers3

1

Use count()..over().

select groupid,offid
from (select groupid,offid,count(*) over(partition by groupid) as cnt
      from tbl
     ) t
where cnt = 1
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
1

Is this what you want?

select e.*
from egr e
where not exists (select 1
                  from egr e2
                  where e2.groupid = e.groupid and e2.offid <> e.offid 
                 );

Or if you want to limit to just those two offers:

select e.*
from egr e
where e.offid in (1, 2) and
      not exists (select 1
                  from egr e2
                  where e2.groupid = e.groupid and 
                        e2.offid in (1, 2) and
                        e2.offid <> e.offid 
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What would be the insert statement for inserting the groupid that the offid 1 is missing (but which offid 2 has)? Basically to add values (1, 404) – Stephane B. May 21 '19 at 21:32
  • @StephaneB. . . . That is a different question and should be asked as a *new* question. – Gordon Linoff May 22 '19 at 01:16
0

This should do it

select groupid from egr group by groupid having count(distinct offid) =1
Sergiu
  • 130
  • 6