1 to many relationship between columns

[Aman]

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.

 

[John Spencer]

Something like the following may work

SELECT Distinct Name, Code
FROM Table
WHERE Table.Name IN (
SELECT Name, Count(Name)
FROM (
SELECT Distinct Name, Code
FROM Table
) as Q
GROUP BY Name
HAVING COUNT > 1)

 

[Aman]

John,

Thanks for your reply. i had just tried exactly what you wrote but
Access was complaining about "WHERE Table.Name IN... " as name is not
the only field in the subquery?? However, on very similar lines the
following worked for me:

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

 

[John Spencer]

My error, I should have seen that.