Simple SQL Help

[paulmac106]

I think it's simple, but I can't get it to work.

In English its: find records in TableA where the field [Field1] has
more than one unique value in Field2

sample records in TableA

Field1 Field2
2241 12345
2241 12345
2242 12345
2242 99856

desired return (2 records)
2242 12345
2242 99856

thank you for your help

Paul

[Plamen Ratchev]

Here is one way to accomplish this:

SELECT Field1, Field2
FROM TableA
WHERE Field1 IN
(SELECT Field1
FROM (SELECT DISTINCT Field1, Field2
FROM TableA) AS A
GROUP BY Field1
HAVING Count(*) 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[paulmac106]

I wish i could get my brain to think like that. Worked perfectly,
thanks for your help.

[--CELKO--]

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