INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

I have a query that's basically lik

I have a query that's basically lik

(OP)
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

RE: I have a query that's basically lik

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

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
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: I have a query that's basically lik

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

Quote (ghbeers)


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

RE: I have a query that's basically lik

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close