Many to Many links - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Many to Many links

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-27 : 08:26:22

Neil writes "I have two tables, one containing the Names of people and criteria upon which they are measured, the other containing Sales data.

The People looks something like :-

Name Measurement Criteria
Fred Yellow or Blue
Bill Yellow
Joe Blue
Anne Red or Yellow

The Sales Data looks like :-

Color Value
Yellow 1000
Blue 2000
Red 500

The result I want is then :-

Name Result
Fred 3000
Bill 1000
Joe 2000
Anne 1500

Is there an easy way to do this, other than to run multiple passes of SQL ??

Thx"

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-27 : 08:55:41

to establish a many to many relationship the common practice is to use a "pass though table" (there are other terms for this) between name and measurement criteria.

you would have a tables with

tblName
-----------------
Nameid
[name]

tblMeasurementCriteria
-------------------------
MeasurementId
Criteria
Value

tblNameMeausurementId
-------------------------
MeasurementId
Nameid

SELECT N.[name],SUM(M.Value) as Value
FROM tblName N
JOIN tblNameMeausurementId NM
ON N.nameid = NM.nameid
JOIN tblMeasurementCriteria M
ON M.MeasurementId = NM.MeasurementId
GROUP BY N.[name]

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources