[Thinus du Pisanie]

Good day people I am struggling with a query problem.

I have the following a Performance_Schedule table ,Performance_KPA,Performance_KPA_KPI,Performance_KPA_Track

Performance_Schedule
Perf_Sched_ID numeric(18,0)
Perf_Sched_Name nvarchar(max)

Performance_KPA
Perf_KPA_ID numeric(18,0)
Perf_Sched_ID numeric(18,0)
KPA_Name nvarchar(max)

Performance_KPA_Track
Perf_KPA_ID numeric(18,0)
Track_Name nvarchar(50)

Performance_KPA_KPI
Perf_KPA_ID numeric(18,0)
Perf_KPI_Name nvarchar(50)

How it works is: ( <-- ) Links to the colum or table
Schedule Name <-- KPA Name <-- Tracking Source Name
KPA_Name <-- KPI Name


Now my statement goes like this witch work absolutely fine but it duplicate the results in report format.

What it does is:(Format)

KPA Name Tracking source Name
KPI Name
KPI Name

But if there is one or more tracking sources linked to the KPA it duplicates the KPA Name like this:

Completing tasks Doc112
KPI Name
KPI Name

Completing tasks Doc113
KPI Name
KPI Name

Now All i want is that it duplicate the tracking source not the whole row like this:
Completing tasks Doc113
Doc112
KPI Name
KPI Name

Here is the statment sovar all that must happpen now is duplicate the result under KPA name and not duplicate the whole statment

SELECT     dbo.Performance_Schedule.Perf_Sched_ID, dbo.Performance_Schedule.Perf_Sched_Name, dbo.Performance_KPA.Perf_KPA_ID, 
                      dbo.Performance_KPA.KPA_Name, dbo.Performance_KPA_KPI.KPI_Name, dbo.Performance_KPA_Track.Track_Name
FROM         dbo.Performance_Schedule INNER JOIN
                      dbo.Performance_KPA ON dbo.Performance_Schedule.Perf_Sched_ID = dbo.Performance_KPA.Perf_Sched_ID INNER JOIN
                      dbo.Performance_KPA_KPI ON dbo.Performance_KPA.Perf_KPA_ID = dbo.Performance_KPA_KPI.Perf_KPA_ID LEFT OUTER JOIN
                      dbo.Performance_KPA_Track ON dbo.Performance_KPA.Perf_KPA_ID = dbo.Performance_KPA_Track.Perf_KPA_ID
WHERE        (Performance_Schedule.Perf_Sched_ID = @Sched_ID) 

I hope you guys can understand what i am trying to achieve here I now code is a bit rusty.
Thanks

[JClephane-Cameron]

Hi Thinus,

Looking at your query I can certainly see that it would duplicate lines for repeats in either Performance_KPA_KPI or Performance_KPA_Track of Perf_KPI_ID

You will only get one row returned if there is only one instance of Perf_KPI_ID in each of these tables. If you want it to combine multiples into a single line (i.e. "Perf_KPI_Name 1, Perf_KPI_Name 2, Perf_KPI_Name 3") then you will need to look at using COALESCE.

Incidentally, I rewrote your code using aliases to help me plot your joins. Here is what I have it as:

SELECT PS.Perf_Sched_ID
, PS.Perf_Sched_Name
, KPA1.Perf_KPA_ID
, KPA1.KPA_Name
, KPA2.KPI_Name
, KPA3.Track_Name
FROM Performance_Schedule PS
INNER JOIN Performance_KPA KPA1
ON PS.Perf_Sched_ID = KPA1.Perf_Sched_ID
INNER JOIN Performance_KPA_KPI KPA2
ON KPA1.Perf_KPA_ID = KPA2.Perf_KPA_ID
LEFT OUTER JOIN Performance_KPA_Track KPA3
ON KPA1.Perf_KPA_ID = KPA3.Perf_KPA_ID
WHERE PS.Perf_Sched_ID = @Sched_ID

>>> Jim <<<