title: select only matching records from two tables
body: <p>I am using Oracle 10g
  Here is my scenario:</p>
  
  <p>I have two tables </p>
  
  <pre><code>class1(groupName, subgroup)
  class2(groupName, subgroup, ind)
  </code></pre>
  
  <p>Here is my data looks like:</p>
  
  <pre><code>class1
  groupName  subgroup
       A      1
       A      2
       B      3
       C      4
       C      4
       C      5
       D      6
  
  
  class2
  groupName  subgroup IND
       A      1        Y     
       A      1        N
       A      2        Y
       A      2        N
       B      3        Y
       C      4        Y
       C      4        N
  </code></pre>
  
  <p>Now, I need to get the data that has the matching groupName and subGroup in both class1 and class2(Not necessarily distinct match). In addition to that the IND column should have pair of 'Y' and 'N' value for each subgroup in class 2.  Eg.In the above example GroupName A is qualified because A exists in both class1 and class2 and it has subgroup 1 and 2 exist in both class1 and class2 and IND column in class2 table has a pair of 'Y' and 'N' for each subgroup (i.e 1 and 2). </p>
  
  <p>Rest of the records are not qualified because : Group B has subgroup 3 that exists in both class1 and class2 but it does not have 'Y' and 'N' pairs for subgroup 3 in class2. Group C and D are not qualified because its all subgroup (4,5) doesn't exist in class2.</p>
  
  <p>I have more than 700,000 records on both table class1 and class2. Anyone has any idea what is the effective way to get this information.</p>