Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

distinct values needed within JOIN query

Posted on 2014-09-08
7
Medium Priority
?
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

670 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