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.
| Author |
Topic |
|
robbyd
Starting Member
2 Posts |
 Posted - 2005-08-16 : 12:11:34
|
| Could someone help me complete this query? Thanks in advance, RobbyThe SP as it stands now is as follows: The procedure takes in a comma delimited string of fProjectIds as well as a comma delimited string of fPlatformIds and returns the following resultset: Select PPX.PlatformId, PMCriticalityId, STCriticalityId, UserCriticalityId, SustAssmtId, TTADescription, PJ.ProjectName, PJ.ProjectId, PT.PlatformName, Requirement from tblProject PJ left join tblPlatformProjectXRef PPX on PPX.ProjectId = PJ.ProjectId left join tblTTA TTA on PPX.TTAVersionId = TTA.TTAVersionId left join tblPlatform PT on PT.PlatformId = PPX.PlatformId where PJ.ProjectId in (" & fProjectIds & ") and PPX.PlatformId in (" & fPlatformIds & ") order by ProjectName, PlatformName The SP needs to be extended a little (or alot). It needs to include not only those records in the tblPlatformProjectXRef table but also those that are not. i.e, I want the resultset to send back all of the current records, but add in "null" values where there is no associated project or platform records in the XREF table. I would expect the result set would look something this if projectIds and platformIds coming in were as follows, respectively: projectIds: 51, 160, 171platformIds: 23,39, 65 (platformId 23 and 65 are not in the tblProjectPlatformXREF for projectId 51 and project 171 is not in the tblProjectPlatformXREF at all) I’ve numbered the columns from the resultset for simplicity. 1 2 3 4 5 6 7 8 9 1051 1 0 3 1 Interest ProjectA 23 PlatformA 1 51 1 0 3 1 Intent ProjectA 39 PlatformB 2 51 1 0 3 1 Commitment ProjectA 65 PlatformC 2 160 NULL NULL NULL NULL NULL ProjectB 23 PlatformA NULL 160 1 3 2 1 Interest ProjectB 39 PlatformB 4 160 2 NULL NULL NULL NULL ProjectB 65 PlatformC NULL 171 NULL NULL NULL NULL NULL ProjectC 23 PlatformA NULL171 NULL NULL NULL NULL NULL ProjectC 39 PlatformB NULL171 NULL NULL NULL NULL NULL ProjectC 65 PlatformC NULL |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Complex SQL... Well, to me it is!