Solved

distinct values needed within JOIN query

Posted on 2014-09-08
7
127 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

861 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