Solved

distinct values needed within JOIN query

Posted on 2014-09-08
7
129 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

752 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