January 7, 2010 at 7:53 am
Hello. I have a PROJECT_TBL that has id references to other tables. IE. resource_id for the RESOURCE_TBL, task_id for the TASK_TBL. I want to create a view that has all of the project detail data in one place. So instead of having the resource_id and task_id I would have the resource from the RESOURCE_TBL, the task from the TASK_TBL and all of the other "flat" data from the PROJECT_TBL. The problem is that there may not be a corresponding resource_id or task_id for a project_id. In other cases there may be. I think I need an outer join for this but not sure how.
Thank you.
January 7, 2010 at 7:57 am
Yes.
It would help if you provided the table definitions (CREATE TABLE statements), sample data for the tables (as a series of INSERT INTO statements for each table), expected results, and what work you have done so far to solve your problem.
January 7, 2010 at 4:33 pm
In the meantime, here is some reading material
http://technet.microsoft.com/en-us/library/ms187956.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2010 at 8:08 pm
jmiaebrown (1/7/2010)
Hello. I have a PROJECT_TBL that has id references to other tables. IE. resource_id for the RESOURCE_TBL, task_id for the TASK_TBL. I want to create a view that has all of the project detail data in one place. So instead of having the resource_id and task_id I would have the resource from the RESOURCE_TBL, the task from the TASK_TBL and all of the other "flat" data from the PROJECT_TBL. The problem is that there may not be a corresponding resource_id or task_id for a project_id. In other cases there may be. I think I need an outer join for this but not sure how.Thank you.
My recommendation would be to teach yourself about this because it's a very common task. With that in mind, lookup outer joins and joins in Books Online (the help system that comes with SQL Server) and read and "play" with the examples until you have a full understanding. Then, write your view.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2010 at 7:31 am
Thank you. I think I am on the right track now. I wasn't familiar with the OUTER JOIN syntax for MSSQL. Coming from Oracle and Sybase the join syntax seemed a bit awkward, but I think i have it. My only concern is that I have 3 different types of 'priorities' that are coming from 1 priority table. I am doing a select for each and then aliasing the columns. I am not sure if there are any adverse impacts to this approach yet. But, here's how my view is defined so far. Please let me know if anything jumps out as a definite, no no. Thank you.
SELECT
dbo.PROJECT_TBL.projectID, dbo.PROJECT_TBL.projectNumber, dbo.PROJECT_TBL.projectName, dbo.PROJECT_TBL.projectCode, dbo.PROJECT_TBL.projectDescription, bo.PROJECT_TBL.projectSummary, dbo.PROJECT_STATUS_TBL.projectStatus, dbo.PROJECT_CATEGORY_TBL.projectCategory, PRIORITY_TBL_1.priority AS projectPriority, dbo.PROJECT_PHASE_TBL.projectPhase, dbo.SITE_TBL.site AS projectSite,
(SELECT priority FROM dbo.PRIORITY_TBL
WHERE (dbo.PROJECT_TBL.BSCPriorityID = priorityID)) AS BSCPriority,
(SELECT priority
FROM dbo.PRIORITY_TBL AS PRIORITY_TBL_2
WHERE (dbo.PROJECT_TBL.PSPriorityID = priorityID)) AS PSPriority,
(SELECT priority
FROM dbo.PRIORITY_TBL AS PRIORITY_TBL_2
WHERE (dbo.PROJECT_TBL.ESCPriorityID = priorityID)) AS ESCPriority, dbo.APPLICATION_TBL.applicationName,
dbo.DEPARTMENT_TBL.departmentName, dbo.PROJECT_TBL.createDt, dbo.PROJECT_TBL.requestDt, dbo.PROJECT_TBL.targetStartDt,
dbo.PROJECT_TBL.targetEndDt, dbo.PROJECT_TBL.actualStartDt, dbo.PROJECT_TBL.actualEndDt, dbo.PROJECT_TBL.reviseDt,
dbo.PROJECT_TBL.auditDt, dbo.PROJECT_TBL.auditor, dbo.PROJECT_TBL.PONum, dbo.PROJECT_TBL.CSRNum, dbo.PROJECT_TBL.ITSRNum,
dbo.PROJECT_TBL.TSProjectManager, dbo.PROJECT_TBL.POProjectManager, dbo.PROJECT_TBL.LOBManager,
dbo.PROJECT_TBL.reqExecReconsider, dbo.PROJECT_TBL.lastUpdDt AS projectLastUpdDt, dbo.PROJECT_TBL.lastUpdBy AS projectLastUpdBy,
dbo.PROJECT_TBL.projectRequestor, dbo.PROJECT_TBL.creatorID, dbo.PROJECT_TBL.isProdSuppInit, dbo.PROJECT_TBL.isOutsidePSInit,
dbo.LOB_TBL.name AS LOB, dbo.PROJECT_DISPOSITION_VIEW.disposition, dbo.PROJECT_DISPOSITION_VIEW.dispositionStatus,
dbo.PROJECT_DISPOSITION_VIEW.dispositionPriority, dbo.PROJECT_DISPOSITION_VIEW.datePresented AS dispositionDtPresented,
dbo.PROJECT_DISPOSITION_VIEW.dateDisposed AS dispositionDtDisposed, dbo.PROJECT_DISPOSITION_VIEW.lastUpdDt AS dispositionLastUpdDt,
dbo.PROJECT_DISPOSITION_VIEW.lastUpdBy AS dispositionLastUpdBy, dbo.PROJECT_HOURS_TBL.reqADSResources,
dbo.PROJECT_HOURS_TBL.ADS_ACT, dbo.PROJECT_HOURS_TBL.ADS_EST, dbo.PROJECT_HOURS_TBL.reqBTSResources,
dbo.PROJECT_HOURS_TBL.BTS_ACT, dbo.PROJECT_HOURS_TBL.BTS_EST, dbo.PROJECT_HOURS_TBL.reqCSResources,
dbo.PROJECT_HOURS_TBL.CS_ACT, dbo.PROJECT_HOURS_TBL.CS_EST, dbo.PROJECT_HOURS_TBL.reqEBSResources,
dbo.PROJECT_HOURS_TBL.EBS_ACT, dbo.PROJECT_HOURS_TBL.EBS_EST, dbo.PROJECT_HOURS_TBL.reqITResources,
dbo.PROJECT_HOURS_TBL.IT_ACT, dbo.PROJECT_HOURS_TBL.IT_EST, dbo.PROJECT_HOURS_TBL.reqLOBResources,
dbo.PROJECT_HOURS_TBL.LOB_ACT, dbo.PROJECT_HOURS_TBL.LOB_EST, dbo.PROJECT_HOURS_TBL.reqOtherResources,
dbo.PROJECT_HOURS_TBL.OTHER_ACT, dbo.PROJECT_HOURS_TBL.OTHER_EST, dbo.PROJECT_HOURS_TBL.reqPOResources,
dbo.PROJECT_HOURS_TBL.PO_ACT, dbo.PROJECT_HOURS_TBL.PO_EST, dbo.PROJECT_HOURS_TBL.reqPSResources,
dbo.PROJECT_HOURS_TBL.PS_ACT, dbo.PROJECT_HOURS_TBL.PS_EST, dbo.PROJECT_HOURS_TBL.reqVNDResources,
dbo.PROJECT_HOURS_TBL.VND_ACT, dbo.PROJECT_HOURS_TBL.VND_EST, dbo.PROJECT_HOURS_TBL.lastUpdBy AS resourcesLastUpdBy,
dbo.PROJECT_HOURS_TBL.lastUpdDt AS resourcesLastUpdDt, dbo.PROJECT_TBL.projectValue, dbo.PROJECT_TBL.projectValueDescription
FROM dbo.PROJECT_TBL LEFT OUTER JOIN
dbo.SITE_TBL ON dbo.PROJECT_TBL.projectSiteID = dbo.SITE_TBL.siteID LEFT OUTER JOIN
dbo.PROJECT_PHASE_TBL ON dbo.PROJECT_TBL.projectPhaseID = dbo.PROJECT_PHASE_TBL.projectPhaseID LEFT OUTER JOIN
dbo.PROJECT_HOURS_TBL ON dbo.PROJECT_TBL.projectID = dbo.PROJECT_HOURS_TBL.projectID LEFT OUTER JOIN
dbo.PROJECT_DISPOSITION_VIEW ON dbo.PROJECT_TBL.projectID = dbo.PROJECT_DISPOSITION_VIEW.projectID LEFT OUTER JOIN
dbo.LOB_TBL ON dbo.PROJECT_TBL.lobID = dbo.LOB_TBL.id LEFT OUTER JOIN
dbo.APPLICATION_TBL ON dbo.PROJECT_TBL.applicationID = dbo.APPLICATION_TBL.applicationID LEFT OUTER JOIN
dbo.DEPARTMENT_TBL ON dbo.APPLICATION_TBL.deptID = dbo.DEPARTMENT_TBL.deptID LEFT OUTER JOIN
dbo.PRIORITY_TBL AS PRIORITY_TBL_1 ON dbo.PROJECT_TBL.BSCPriorityID = PRIORITY_TBL_1.priorityID AND
dbo.PROJECT_TBL.PSPriorityID = PRIORITY_TBL_1.priorityID AND dbo.PROJECT_TBL.ESCPriorityID = PRIORITY_TBL_1.priorityID AND
dbo.PROJECT_TBL.projectPriorityID = PRIORITY_TBL_1.priorityID LEFT OUTER JOIN
dbo.PROJECT_CATEGORY_TBL ON dbo.PROJECT_TBL.projectCategoryID = dbo.PROJECT_CATEGORY_TBL.projectCategoryID LEFT OUTER JOIN
dbo.PROJECT_STATUS_TBL ON dbo.PROJECT_TBL.projectStatusID = dbo.PROJECT_STATUS_TBL.projectStatusID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply