SQL Server – объединение запросов с соответствующими значениями в двух столбцах

Очень новичок в Sql Server, и я хочу объединить два запроса, сопоставляя два столбца. Например, если столбец TimeByDay & ResourceName в запросе Capacity соответствует столбцу TimeByDay и ResourceName в запросе Allocation, то вытащите значения в Capacity. Любые другие рекомендации приветствуются. Пожалуйста, дайте мне знать, если я не понимаю. Благодарю.

QueryAllocation

ProjectName TaskName ResourceName ResourceManager AssignmentWork TimeBYDay Project1 Task1 ResourceA ManagerA 8 2015-1-1 Project1 Task2 ResourceB ManagerB 7.5 2015-1-2 Project2 Task3 ResourceA ManagerA 4 2015-1-5 Project3 Task4 ResourceA ManagerA 1 2015-1-5 Project3 Task5 ResourceB ManagerB 2 2015-1-5 Project4 Task6 ResourceC ManagerB 2 2015-1-5 

запрос

 SELECT P.ProjectName, T.TaskName, R.ResourceName, RUV.[Resource Manager], ABD.AssignmentWork, TBD.TimeByDay FROM dbo.MSP_TimeByDay TBD LEFT JOIN dbo.MSP_EpmAssignmentByDay ABD ON TBD.TimeByday = ABD.TimeByDay LEFT JOIN dbo.MSP_EpmAssignment A ON ABD.AssignmentUID = A.AssignmentUID LEFT JOIN dbo.MSP_EpmResource R ON A.ResourceUID = R.ResourceUID LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON R.ResourceUID = RUV.ResourceUID LEFT JOIN dbo.MSP_EpmTask T ON A.TaskUID = T.TaskUID LEFT JOIN dbo.MSP_EpmProject P ON T.ProjectUID = P.ProjectUID WHERE NOT (ABD.AssignmentUID IS NULL) 

QueryCapacity

 ResourceName Capacity TimeByDay ResourceA 6 2015-1-1 ResourceB 4 2015-1-2 ResourceA 8 2015-1-5 ResourceB 7 2015-1-5 ResourceC 7.5 2015-1-5 

запрос

 SELECT R2.ResourceName, RBD2.Capacity, TBD2.TimeByDay FROM dbo.MSP_TimeByDay TBD2 LEFT JOIN dbo.MSP_EpmResourceByDay RBD2 ON TBD2.TimeByDay = RBD2.TimeByDay LEFT JOIN dbo.MSP_EpmResource R2 ON RBD2.ResourceUID = R2.ResourceUID WHERE NOT (RBD2.ResourceUID IS NULL) 

Желаемые результаты

 ProjectName TaskName ResourceName ResourceManager AssignmentWork TimeBYDay Capacity Project1 Task1 ResourceA ManagerA 8 2015-1-1 6 Project1 Task2 ResourceB ManagerB 7.5 2015-1-2 4 Project2 Task3 ResourceA ManagerA 4 2015-1-5 8 Project3 Task4 ResourceA ManagerA 1 2015-1-5 8 Project3 Task5 ResourceB ManagerB 2 2015-1-5 7 Project4 Task6 ResourceC ManagerB 2 2015-1-5 7.5 

Спасибо SimplyInk, я не знал, что конструктор запросов можно открыть в SSMS. Я получил желаемые результаты.

 SELECT dbo.MSP_EpmAssignment.AssignmentUID, dbo.MSP_EpmAssignmentByDay.AssignmentWork, dbo.MSP_EpmAssignmentByDay.TimeByDay, dbo.MSP_EpmResourceByDay.Capacity, dbo.MSP_EpmTask.TaskName, dbo.MSP_EpmProject.ProjectName, dbo.MSP_EpmResource.ResourceName, dbo.MSP_EpmResource_UserView.[Resource Manager], dbo.MSP_EpmResource_UserView.[Resource Departments], dbo.MSP_EpmResource_UserView.[Resource Role], dbo.MSP_EpmResource_UserView.ResourceEarliestAvailableFrom, dbo.MSP_EpmResource_UserView.ResourceLatestAvailableTo, dbo.MSP_TimeByDay.TimeMonthOfTheYear, dbo.MSP_TimeByDay.TimeDayOfTheMonth, dbo.MSP_TimeByDay.TimeYear FROM dbo.MSP_EpmAssignment INNER JOIN dbo.MSP_EpmAssignmentByDay ON dbo.MSP_EpmAssignment.AssignmentUID = dbo.MSP_EpmAssignmentByDay.AssignmentUID INNER JOIN dbo.MSP_EpmResourceByDay ON dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResourceByDay.ResourceUID AND dbo.MSP_EpmAssignmentByDay.TimeByDay = dbo.MSP_EpmResourceByDay.TimeByDay INNER JOIN dbo.MSP_EpmTask ON dbo.MSP_EpmAssignment.ProjectUID = dbo.MSP_EpmTask.ProjectUID AND dbo.MSP_EpmAssignment.TaskUID = dbo.MSP_EpmTask.TaskUID AND dbo.MSP_EpmAssignmentByDay.ProjectUID = dbo.MSP_EpmTask.ProjectUID AND dbo.MSP_EpmAssignmentByDay.TaskUID = dbo.MSP_EpmTask.TaskUID INNER JOIN dbo.MSP_EpmProject ON dbo.MSP_EpmAssignment.ProjectUID = dbo.MSP_EpmProject.ProjectUID AND dbo.MSP_EpmAssignmentByDay.ProjectUID = dbo.MSP_EpmProject.ProjectUID AND dbo.MSP_EpmTask.ProjectUID = dbo.MSP_EpmProject.ProjectUID INNER JOIN dbo.MSP_EpmResource ON dbo.MSP_EpmAssignment.ResourceOwnerUID = dbo.MSP_EpmResource.ResourceUID AND dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResource.ResourceUID AND dbo.MSP_EpmResourceByDay.ResourceUID = dbo.MSP_EpmResource.ResourceUID INNER JOIN dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmResourceByDay.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN dbo.MSP_TimeByDay ON dbo.MSP_EpmAssignmentByDay.TimeByDay = dbo.MSP_TimeByDay.TimeByDay 

Мне нужно следующее соединение

 INNER JOIN dbo.MSP_EpmResourceByDay ON dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResourceByDay.ResourceUID AND dbo.MSP_EpmAssignmentByDay.TimeByDay = dbo.MSP_EpmResourceByDay.TimeByDay 

Я не уверен, что такое схема и отношения между вашими таблицами, поэтому я делаю снимок в темноте.

Пожалуйста, проверьте, дает ли этот запрос результаты в нужном вам формате.

Вам нужно будет проверить, что условия соединения не создают ненужных дубликатов из-за использования LEFT JOIN .

 SELECT P.ProjectName , T.TaskName , R.ResourceName , RUV.[Resource Manager] , ABD.AssignmentWork , TBD.TimeByDay , RBD.Capacity FROM dbo.MSP_TimeByDay TBD -- Query Allocation LEFT JOIN dbo.MSP_EpmAssignmentByDay ABD ON TBD.TimeByday = ABD.TimeByDay LEFT JOIN dbo.MSP_EpmAssignment A ON ABD.AssignmentUID = A.AssignmentUID LEFT JOIN dbo.MSP_EpmResource R ON A.ResourceUID = R.ResourceUID LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON R.ResourceUID = RUV.ResourceUID LEFT JOIN dbo.MSP_EpmTask T ON A.TaskUID = T.TaskUID LEFT JOIN dbo.MSP_EpmProject P ON T.ProjectUID = P.ProjectUID -- Query Capacity LEFT JOIN dbo.MSP_EpmResourceByDay RBD ON TBD.TimeByDay = RBD.TimeByDay WHERE NOT (ABD.AssignmentUID IS NULL) AND NOT (RBD.ResourceUID IS NULL) 
Interesting Posts
Давайте будем гением компьютера.