1 to many relationship between columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aman - 09 Feb 2006 17:31 GMT

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.

Reply to this Message

inkman04 - 09 Feb 2006 19:04 GMT

Perhaps what you can do is run a grouping query
from the table source and add only the Name field
to it, TWICE. The first field to be set as Group By
and the second set as Count. Then, set the
criteria for the Count column to >1. This will get
only the records that have 2 or more in your dataset.

Next, create a second query with the table as it's
source also and add your fields Name & Code. Also,
whatever fields you need.
Then, add the previous query and just link the two
Name fields together.

Add the fields from the table souce to the QBE grid
and set them both to ascending. Now run the query.

You should end up with the records you require.

Have a nice day.

Regards

Reply to this Message

MGFoster - 09 Feb 2006 20:05 GMT

Try:

SELECT [Name], [Code]
FROM table_name As T
GROUP BY [Name], [Code]
HAVING (SELECT COUNT(*) FROM table_name
WHERE [Name] = T.[Name]) > 1

This won't work if you include the other columns (fields). You'll have
to make it a derived table:

SELECT [Name], [Code], <other columns>
FROM
( SELECT [Name], [Code]
FROM table_name As T
GROUP BY [Name], [Code]
HAVING (SELECT COUNT(*) FROM table_name
WHERE [Name] = T.[Name]) > 1
) AS A

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hi,
>
[quoted text clipped - 26 lines]
> Access but would like to have an understanding of how this should be
> generally handled.

Reply to this Message

Aman - 09 Feb 2006 20:37 GMT

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

The solution you had proposed Foster did not exclude the unique
combinations of Name and Code, and as I menntioned I did not want them.
I just wanted the names and codes for cases where multiple codes
existed for a name.

Thanks alot!!

Reply to this Message