0

Suppose there are 2 tables as shown below. Table A contains rows in a few groups - 11 (1,2,3) and 22 (4,5,6,7) in this case. Table B contains book rows that indirectly reference groups in table A via B.a_id = A.a_id foreign key relationship):

Table A:
a_id  grp
---------
1     11
2     11
3     11

4     22
5     22
6     22
7     22

Table B:
b_id a_id book
--------------
1    1    AA
2    2    AA
3    3    AA

4    1    BB
5    2    BB
6    3    BB

7    1    CC
8    3    CC

9    4    AA
10   5    AA
11   6    AA

12   4    BB
13   5    BB
14   6    BB
15   7    BB

16   6    CC
17   7    CC

I need to select only those book/group combinations that are complete, i.e.

a_id  grp  book
---------------
1     11   AA
2     11   AA
3     11   AA

1     11   BB
2     11   BB
3     11   BB

4     22   BB
5     22   BB
6     22   BB
7     22   BB

11 CC, 22 AA or 22 CC are not eligible, as they do not represent complete groups. One way to get the desired result is to use row counts by each group in a subquery, but its seems kind of shaky. Any other ideas?

Thanks!

Edit: I don't think that the referenced question is really the same problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
uncoder
  • 1,818
  • 1
  • 17
  • 20
  • If 1, 2, 3, 4 were in a group would it be complete for "A"? And what database are you using? – Gordon Linoff Jan 05 '15 at 21:17
  • My db is Oracle 11g. As for the 1st part of your question, I don't think I understand what you mean. – uncoder Jan 05 '15 at 21:21
  • There is a lot of useful information in this answer: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation. However, note that much of the SQL in the top answer is not acceptable in Oracle. – Gordon Linoff Jan 05 '15 at 21:45
  • 1
    Gordon, I am familiar with that concept, thanks, although I could not figure out how to adapt it to this problem. – uncoder Jan 05 '15 at 22:19

1 Answers1

0

This is what I could come up with:

select
  A.a_id, A.grp, B.book
from
  A
  inner join B
  on A.a_id = B.a_id

  left outer join
  (select distinct A.grp, B.book
   from A, B
   where not exists
    (
    select *
    from A a2
      inner join B b2
      on a2.a_id = b2.a_id
    where
      a2.a_id = A.a_id
      and a2.grp = A.grp
      and b2.book = B.book
    )
  ) T1 -- < -- represents incomplete book/group combos
  on A.grp = T1.grp
  and B.book = T1.book
where
  T1.grp is null -- < -- exclude incomplete book/group combos
uncoder
  • 1,818
  • 1
  • 17
  • 20