Complex Query Question 1

[Guru7777]

tblSituation
Id Name
1 Situation A
2 Situation B
3 Situation C
4 Situation D

tblPeople
Id Name
1 Person A
2 Person B
3 Person C
4 Person D

tblIncident
Id Time Resolved Name
1 1:00 1 IncidentA
2 2:00 0 IncidentB
3 3:00 0 IncidentC

tblIncidentSituation
Id SitId IncidentId
1 1 1
2 2 1
3 1 3
4 2 3
5 3 3
6 4 3

tblIncidentPeople
Id PplId IncidentId
1 1 1
2 2 1
3 1 3
4 2 3
5 3 3
6 4 3

Notice how each incident can have many people and many situations assigned to it? How can a T-SQL query be written so that the following result can be obtained? (Just in pseudocode - like call cursor do this join, etc, no need to write the code if you don't want to)

IncidentId IncidentName People Situation
1 IncidentA PersonA,PersonB SituationA,SituationB
3 IncidentC PersonA,PersonB,PersonC,PersonD SituationA,SituationB,SituationC,SituationD


Notice how the results of a many to many are in one column in the result. What is the best way to tackle this?

Much thanks.

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[mrdenny]

Most tipically this would be done via your user interface.

If it must be done in T/SQL it can be done via functions.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

Here's a thread thread183-1159740 where something similar is done. Like MrDenny says, you will probably want to implement this through functions.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Guru7777]

That thread ended up having the answer. Thanks.

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[gmmastros]

You're welcome.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom