Hi,

Considering a table/dataset of structure below:

Name, Code, Other fields
ABC, 1, ...........
ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1, ............
DEF, 2,............
GHI, 4, ............
JKL, 5,............
......
......

I want to get a result set which would give me all instances where
multiple "Code" exist for each "Name". The result set should look like:

ABC, 1,............
ABC, 2,............
ABC, 3,............
DEF, 1,............
DEF, 2,............
(notice since there aren't multiple codes for "GHI" and "JKL", these
are not included in the resultset)

Can someone please suggest a SQL for this. I am trying this in MS
Access but would like to have an understanding of how this should be
generally handled.

Thanks.

i think this will work.....

select name,code from <tblname> where name in (select name from
<tblname> group by name having count(*) > 1)

Thanks for your reply rajeev but this does not work. The query you've
written brings back all rows where the name exists more than once. That
is not the required result here.

Hi,

Aman wrote:
Assuming that the unspecified other columns include (or amount together to)
a unique primary key, you could:

SELECT DISTINCT T1.Name, T1.Code
    FROM YourTable AS T1
    JOIN YourTable AS T2 ON T1.Name = T2.Name AND T1.UniqueKey <>
T2.UniqueKey

Thanks for your replies guys. It did give me some ideas on how to
approach this. Here's what worked for me in MS Access:

SELECT DISTINCTROW tbl_nm.name, tbl_nm.code
FROM tbl_nm, [select distinct name, count(*)
                         from (
                               SELECT DISTINCTROW tbl_nm.Name,
tbl_nm.code, Count(*)
                               FROM tbl_nm
                               GROUP BY tbl_nm.Name, tbl_nm.code)
                          group by name
                          having count(*) > 1]. AS abc
WHERE tbl_nm.name = abc.name
GROUP BY tbl_nm.name, tbl_nm.code

Thanks alot!!

Please do a better job of posting DDL in the future; this was weak even
for pseudo-code.  At least tip your hat to ISO-11179 rules with a dummy
entity name, like Foobar.  This query will give you just the names:

SELECT foo_name
  FROM Foobar
GROUP BY foo_name
HAVING COUNT(DISTINCT foo_code) > 1;

The trick is the COUNT(DISTINCT <exp>) to handle one code repeated many
times. If you want the actual codes, then

SELECT DISTINCT F1.foo_name, F1.foo_code
  FROM Foobar AS F1
WHERE F1.foo_name
       IN (SELECT F2.foo_name
             FROM Foobar AS F2
            GROUP BY F2.foo_name
           HAVING COUNT(DISTINCT foo_code) > 1);

Using FKs to non PK unique-indexes
dynmica use @db
sql Help
Get the top 2 sold without using temp table.
SELECT TOP 12 skips a record!
Help importing legacy FoxPro db into SQL 2000
SELECT info from table on a different server
Executing Stored Procedure within Trigger
Difference Function
Cursor