I have a query that's basically lik

[ghbeers]

I have a query that's basically like and the output looks like what follows it.

I can get the 3rd party reporting tool in which I place the sql script to take care of everything but
what you see for the repeating of the statuses and catalog years.

Is there a way to make these columns independent and just list as they are? I've tried using temp tables, CTEs but still get the same results.

SELECT P.PROGRAM_ID,
PL.TYPE,
S.STATUSES,
PL.CATALOG_YEARS,
PL.DEPARTMENT

FROM ACAD_PROGRAMS P
LEFT JOIN ACAD_PROGRAMS_LS PL ON P.PROGRAM_ID = PL.PROGRAM_ID -- (MULTIPLE VALUES FOR PROGRAM_ID)
LEFT JOIN STATUSES S ON P.PROGRAM_ID = S.PROGRAM_ID -- (MULTIPLE VALUES FOR PROGRAM_ID)

program id type statuses catalog years department

ABC XX A 2015 DEP1
ABC XX P 2015 DEP1
ABC XX I 2015 DEP1
ABC XX A 2016 DEP1
ABC XX P 2016 DEP1
ABC XX I 2016 DEP1
ABC XX A 2017 DEP1
ABC XX P 2017 DEP1
ABC XX I 2017 DEP1

Would like the output to be

ABC XX A 2015 DEP1
ABC XX P 2016 DEP1
ABC XX I 2017 DEP1

 

[Andrzejek]

What makes the [blue]BLUE[/blue] records so special?
What's the logic of including BLUE and excluding BLACK records?

[pre]program id type statuses catalog years department[blue]
ABC XX A 2015 DEP1[/blue]
ABC XX P 2015 DEP1
ABC XX I 2015 DEP1
ABC XX A 2016 DEP1[blue]
ABC XX P 2016 DEP1[/blue]
ABC XX I 2016 DEP1
ABC XX A 2017 DEP1
ABC XX P 2017 DEP1[blue]
ABC XX I 2017 DEP1[/blue]
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SgtJarrow]

To expand on Andy's response, I believe the issue lies in your JOIN statements.

LEFT JOIN ACAD_PROGRAMS_LS PL ON P.PROGRAM_ID = PL.PROGRAM_ID -- (MULTIPLE VALUES FOR PROGRAM_ID)
LEFT JOIN STATUSES S ON P.PROGRAM_ID = S.PROGRAM_ID -- (MULTIPLE VALUES FOR PROGRAM_ID)

The emphasis you provided by stating that there were "(MULTIPLE VALUES FOR PROGRAM_ID)" indicates your JOIN statement will need more than one clause in order to match the records properly.

Andy is asking you to provide some additional logic and I am also suggesting you post an example of each of the tables as well....columns and data types.

These will be necessary to find the additional portion of the JOIN clause(s) needed.

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[ghbeers]

Sorry it's taken me so long to respond. Been too busy with my work.

I've tried many ways to structure the query. Below is the basic query. I tried pulling out the depts and catalogs into separate CTEs with no success. Perhaps there's no better way to do this. Will the user have to settle for some repeating values like in the statuses? The 3rd party reporting tool does eliminate the duplicates like the AP.ACAD_PROGRAMS_ID, but can't help with the repeating values seen in my attached example. See attachment with table structure and example.

select
AP.ACAD_PROGRAMS_ID,
APS.ACPG_STATUS,
APS.ACPG_STATUS_DATE,
APL.ACPG_TYPES,
APL.ACPG_CATALOGS,
APL.ACPG_DEPTS,
DEP_DEPTS_DIVISION,
DEP_DEPTS_SCHOOL,
APL.ACPG_MAJORS,
APL.ACPG_MINORS

FROM ACAD_PROGRAMS AP
LEFT OUTER JOIN ACAD_PROGRAMS_LS APL ON AP.ACAD_PROGRAMS_ID = APL.ACAD_PROGRAMS_ID
LEFT OUTER JOIN PROGRAM_STATUS APS ON AP.ACAD_PROGRAMS_ID = APS.ACAD_PROGRAMS_ID
LEFT OUTER JOIN PROGRAM_STATUS APS2 ON AP.ACAD_PROGRAMS_ID = APS2.ACAD_PROGRAMS_ID
AND APS2.POS = 1
LEFT OUTER JOIN DEPTS DEP ON APL2.ACPG_DEPTS = DEP.DEPTS_ID

WHERE APS2.ACPG_STATUS = <<Program Status[Text]>>

ORDER BY DEP.DEPTS_DIVISION, APL.ACPG_DEPTS, APS2.ACPG_STATUS, AP.ACAD_PROGRAMS_ID