matching attributes in two columns

Nostromo77 · June 21st, 2011, 04:08 PM

Hello,

I need help constructing an SQL query to find matching data in a table across two columns (Oracle 11g, but this is so basic I suspect the exact brand of SQL doesn't matter).

I have a large table with a PK of 'ModuleItemID', a 'ModuleNumber' column matching to a lookup table, and a 'PartNo' column. A single ModuleNumber can be associated with many PartNo's, but each PartNo should only appear with each ModuleNumber once.

I need to root out several instances of the above - "find all ModuleItemID rows with a particular ModuleNumber and PartNo where count >1" - but I'm not sure what the exact syntax is.

In the example below, I want to find duplicate rows like 1 & 3 and 7 & 9:

<code>
ModuleItemID ModuleNumber PartNo
1 1 161-4514
2 1 168-0056
3 1 161-4514
4 2 160-4514
5 2 168-0056
6 3 160-4514
7 3 171-0023
8 3 181-0565
9 3 171-0023
</code>

Thanks for any advice!

Nostromo77 · June 22nd, 2011, 12:03 PM

I seem to have found a solution; the following Query did the trick:

Code:

SELECT MODULE_CD, COUNT(MODULE_CD), PART_NO, COUNT(PART_NO)
FROM MYTABLE
GROUP BY MODULE_CD, PART_NO
HAVING (COUNT(MODULE_CD) > 1) AND (COUNT(PART_NO) > 1)