Solved

distinct values needed within JOIN query

Posted on 2014-09-08
7
125 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

920 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

12 Experts available now in Live!

Get 1:1 Help Now