Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Angry Unanswered: multi-table join problem

    I have three tables that all contain different types of data but have a customer name in common. I can join the three of them into one view that gives me an overview of what issues they are having by using the following query:

    SELECT dbo.ProjectContentCount.SiteName AS SiteContent, dbo.ProjectIssueCount.SiteName AS SiteIssue,
    dbo.ProjectFeatureCount.SiteName AS SiteFeature, dbo.ProjectContentCount.[Open] AS OpenContent,
    dbo.ProjectContentCount.Fixed AS FixedContent, dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectIssueCount.[Open] AS OpenIssue,
    dbo.ProjectIssueCount.Pending AS PendingIssue, dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
    dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
    FROM dbo.ProjectFeatureCount FULL OUTER JOIN
    dbo.ProjectIssueCount ON dbo.ProjectFeatureCount.SiteName = dbo.ProjectIssueCount.SiteName FULL OUTER JOIN
    dbo.ProjectContentCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectContentCount.SiteName AND
    dbo.ProjectFeatureCount.SiteName = dbo.ProjectContentCount.SiteName

    This works fine because it gets a list of all clients and data for whichever row there exists data of that type, whether or not there is data for a given client on each table.

    However, I now need to join this group of data to a master client table that contains contact information. The master table has info about all the clients but every join I try breaks the join I have working above and causes duplicate rows.

    Any ideas would be greatly appreciated!

  2. #2
    Join Date
    Jun 2004
    Posts
    4
    Well, I found my answer after many iterations in query analyzer and here is what I came up with. I hope it helps someone else...

    SELECT dbo.SiteMaster.ItemID, dbo.SiteMaster.ModuleID, dbo.SiteMaster.SiteID, dbo.SiteMaster.SiteManager, dbo.SiteMaster.SiteStatus,
    dbo.SiteMaster.SiteName, dbo.ProjectIssueCount.[Open] AS OpenIssue, dbo.ProjectIssueCount.Pending AS PendingIssue,
    dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectContentCount.[Open] AS OpenContent, dbo.ProjectContentCount.Fixed AS FixedContent,
    dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
    dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
    FROM dbo.ProjectContentCount FULL OUTER JOIN
    dbo.ProjectIssueCount FULL OUTER JOIN
    dbo.SiteMaster ON dbo.ProjectIssueCount.SiteName = dbo.SiteMaster.SiteName FULL OUTER JOIN
    dbo.ProjectFeatureCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectFeatureCount.SiteName AND
    dbo.SiteMaster.SiteName = dbo.ProjectFeatureCount.SiteName ON dbo.ProjectContentCount.SiteName = dbo.SiteMaster.SiteName AND
    dbo.ProjectContentCount.SiteName = dbo.ProjectIssueCount.SiteName

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •