Select from many to many table

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 2005 Forums

Transact-SQL (2005)

Select from many to many table

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-17 : 12:01:26

Ok what I have are 3 tables with the middle table being my many to many relationship table. What I need to do is list 1 process to all of the TagProcesses. So here are the tables

tblProcess
intProcessID
strProcessNum
StrProcessName

tblTagProcess
intTagprocessId
strTagProcessNum
strProcessName

tblTagProcessToProcess
intProcessId
intTagProcssId

So what I need to do is list all the strTagProcessNum under tblTagProcess that belong to the strProcessNum under tblProcess.

So it would look like this. I want to be able to combine all the Tag process to one field. how can i accomplish this.

strProcessNum strTagProcessNum
P1 C1, C2,
P2 F1, C2
P15 C3, F1
P16 F3, F2

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-17 : 13:36:04

The general recommedation is to do this concatenation in the front end as opposed to T-SQL.

Here's a good article on most of the options to concatenate row values:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

There was a popular method using COALESCE. But the "FOR XML PATH('')" method seems the preferred one now.

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources