Posted by --CELKO-- on 03/10/07 14:05

>> find records [sic] in TableA where field1 [sic] has more than one unique value in field2 <<

Your firtst problem is conceptual; rows are not records; fields are
not columns; tables are not files. Next, think about the phrase "more
than one unique value" versus "more than one non-unique value". SQL
would prefer the phrase "without redundant dupicates" or something.

The vague narrative you posted is not a table at all -- it has no
key! That is one of the MANY differences between rows and records.
In short, if you did things right this would not be a problem at all.

CREATE TABLE Foobar
(field1 INTEGER NOT NULL PRIMARY KEY,
field2 INTEGER NOT NULL);

or maybe you meant:

CREATE TABLE Foobar
(field1 INTEGER NOT NULL,
field2 INTEGER NOT NULL,
PRIMARY KEY (field1, field2));

Who knows from that vague narrative? This is why you post DDL even for
the simple, short things. But using a guess at your original non-
table:

SELECT T1.field1, T1.field2
FROM NonTable AS T1
WHERE T1.field1
IN (SELECT T2.field1
FROM NonTable AS T2
GROUP BY T2.field1
HAVING MIN(T2.field2 <> MAX(T2.field2);

Look at this versus Plamen's solution. Once you can think in sets,
instead records and fields, there is no need to use horrible nested
subqueries for such problems. Plamen also has a SELECT DISTINCT with
a GROUP BY that is expensive and redundant.

[Back to original message]