[SOLVED] select only matching records from two tables

select only matching records from two tables

I am using Oracle 10g Here is my scenario:

I have two tables

class1(groupName, subgroup)
class2(groupName, subgroup, ind)

Here is my data looks like:

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

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).

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.

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.

Solution

Does this create what you need?

SELECT *
FROM class1 c1
JOIN class2 c2 ON c1.groupName = c2.groupName
        AND c1.subgroup = c2.subgroup
WHERE
    (
    SELECT COUNT(DISTINCT ind)
    FROM class2 c2a
    WHERE c2a.groupName = c1.groupName
        AND c2a.subgroup = c2a.subgroup
    ) = 2
  AND
    (
    SELECT COUNT(DISTINCT subgroup)
    FROM class1 c1b
    WHERE c1b.groupName = c1.groupName
    ) =
    (
    SELECT COUNT(DISTINCT subgroup)
    FROM class2 c2b
    WHERE c2b.groupName = c2.groupName
    )