Solved

distinct values needed within JOIN query

Posted on 2014-09-08
7
124 Views
Last Modified: 2014-09-08
Here is the query I am using in sql

select g.Agency,g.Goal, o.Objective,p.PI,p.PIID,P.Method,a.[ActivityName],A.ActivityID,'' as Count, '' as Hours,'' as ProgressSetback, '' as Month
from tblOrgGoals G 
Join tblOrgObjectives O on G.GoalID = O.GoalID and g.agencyid=o.agencyid 
 Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID and g.agencyid=a.agencyid 
 Join tblOrgPI P on P.ActivityID = A.ActivityID and g.agencyid=p.agencyid 
LEFT Join tblOrgMonthlyProgressSetbacks S on P.PIID = S.PIID and g.agencyid=s.agencyid 
Where G.AgencyID = '74' And A.Fiscal = 2015  order by G.Agency,G.Goal

Open in new window


Attached is my output.  I am trying to get Distinct PIID values and its not coming up that way as indicated by the attached results.  what modifications do I need to achieve the desired output
Book2.xlsx
0
Comment
Question by:al4629740
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 40311179
I am not sure why it would be distinct. You are not using a group by or distinct keyword in your query. Is piid a primary key in tblorgpi?

In any event, try doing this:

select ..... Where G.AgencyID = '74' And A.Fiscal = 2015  GROUP BY P.PIID
order by G.Agency,G.Goal
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40311186
Have you tried SELECT DISTINCT ?

I am not fond of that as a solution but it may be OK in this query.

However: If you actually are counting and summing than that won't be sufficient
0
 

Author Comment

by:al4629740
ID: 40311227
mankowitz:

I get Msg 8120, Level 16, State 1, Line 1
Column 'tblOrgGoals.Agency' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Paul:
Can you provide example?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40311248
Place the word DISTINCT after SELECT in your existing in query
SELECT DISTINCT
      g.Agency
    , g.Goal
    , o.Objective
    , p.PI
    , p.PIID
    , P.Method
    , a.[ActivityName]
    , A.ActivityID
    , '' AS Count
    , '' AS Hours
    , '' AS ProgressSetback
    , '' AS Month
FROM tblOrgGoals G
      JOIN tblOrgObjectives O
                  ON G.GoalID = O.GoalID
                        AND g.agencyid = o.agencyid
      JOIN tblOrgActivities A
                  ON O.ObjectiveID = A.ObjectiveID
                        AND g.agencyid = a.agencyid
      JOIN tblOrgPI P
                  ON P.ActivityID = A.ActivityID
                        AND g.agencyid = p.agencyid
      LEFT JOIN tblOrgMonthlyProgressSetbacks S
                  ON P.PIID = S.PIID
                        AND g.agencyid = s.agencyid
WHERE G.AgencyID = '74'
      AND A.Fiscal = 2015
ORDER BY
      G.Agency, G.Goal

Open in new window

However as I said before I don't normally propose this as a solution.
see:  Select Distinct is returning duplicates ...
0
 

Author Comment

by:al4629740
ID: 40311270
It seems to work...

What would you suggest instead, if you don't normally propose this...?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40311294
What would you suggest instead ...?

GROUP BY
and/or DERIVED TABLE
and/or more JOIN CONDITIONS
and/or ROW_NUMBER()  in a derived table
... could be others

Each query needs to be addressed on its own merits.

Please read my article for more on the topic I don't wish to repeat it here. The point is "select distinct" is fine "in some situations". That article links to another I would draw your attention to:
DISTINCT and GROUP BY... and why does it not work for my query?

I just do NOT want to indicate "select distinct" is a generic cure all solution because, quite frankly, it is over used and often is the cause of performance issues. My message to you is please don't consider it as a "cure all"! Do read my article and at the end are some further references.
0
 

Author Comment

by:al4629740
ID: 40311298
ok
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now