×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Stuck on getting only first valid record from associated table

Stuck on getting only first valid record from associated table

Stuck on getting only first valid record from associated table

(OP)
I am absolutely stuck on finding ONLY 1 record that exists in an associated table that meets my minimum requirement. I have a table STUDENT_TERMS that is for a student and specific term. In the table are keys to all of the course info the student has signed up for the semester. When I go to the course info records (STUDENT_ACAD_CRED), I want to stop after I find the first record where the grade is not NULL. Everything I've tried is not working or it takes way too long to execute. Here is my script so far. The last CTE is the ome I just can't get right, really missing something. Any help will be greatly appreciated.

-- Student with 0 GPA or Completed credits for Term
WITH USER_TERM AS
(
SELECT DISTINCT
T.TERMS_ID AS 'USR_TERM',
TL.TERM_CENSUS_DATES AS 'CUR_CENSUS_DATE',
T.TERMS_ID + '*UG' AS 'STTR_TERM',
T.TERM_END_DATE AS 'TERM_END'
FROM TERMS T
LEFT JOIN TERMS_LS TL ON T.TERMS_ID = TL.TERMS_ID
WHERE TL.TERM_CENSUS_DATES IS NOT NULL
AND TL.POS = 1
AND TL.TERMS_ID = '2017/FA'
-- AND TL.TERMS_ID = UPPER( <<Enter Term YYYY/SS [Text]>> )
)
--SELECT * FROM USER_TERM
,RPT_STTR_STUS AS
(
SELECT DISTINCT TOP 60
SUBSTRING(STTR.STUDENT_TERMS_ID, 1,7) 'STUDENT_ID',
STTR.STUDENT_TERMS_ID 'STTR_KEY',
STTRS.STTR_STATUS 'STTR_STATUS'

FROM STUDENT_TERMS STTR
JOIN USER_TERM UT ON STTR.STUDENT_TERMS_ID LIKE '%' + UT.STTR_TERM
JOIN STTR_STATUSES STTRS ON STTR.STUDENT_TERMS_ID = STTRS.STUDENT_TERMS_ID
AND STTRS.POS = 1
AND STTRS.STTR_STATUS IN ('R','P','T')
)
--SELECT * FROM RPT_STTR_STUS
,HAVE_GRADE_STUS AS
(
SELECT DISTINCT
RSS.STUDENT_ID 'STUDENT_ID'
FROM RPT_STTR_STUS RSS
WHERE RSS.STUDENT_ID =
(
SELECT TOP 1 STAC1.STC_PERSON_ID
FROM STUDENT_ACAD_CRED STAC1
JOIN STUDENT_TERMS_LS STTRL ON STAC1.STUDENT_ACAD_CRED_ID = STTRL.STTR_STUDENT_ACAD_CRED
AND STAC1.STC_VERIFIED_GRADE IS NOT NULL
)
)

SELECT * FROM HAVE_GRADE_STUS

RE: Stuck on getting only first valid record from associated table

not clear what you are after. Could you please give us a sample of inputs for each table and desired output.

As for being slow "JOIN USER_TERM UT ON STTR.STUDENT_TERMS_ID LIKE '%' + UT.STTR_TERM" will most likely be a killer

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Stuck on getting only first valid record from associated table

(OP)
I figured this out about 2 seconds before I saw you response come in. Here's what I ended up doing with the last CTE that is working correctly. Thank you for getting back to me.

HAVE_GRADE_STUS AS
(
SELECT distinct
RPT.STTR_KEY
FROM RPT_STTR_STUS RPT
JOIN STUDENT_TERMS_LS STTR ON RPT.STTR_KEY = STTR.STUDENT_TERMS_ID
LEFT JOIN (SELECT STAC.STUDENT_ACAD_CRED_ID AS STAC_KEY,
Row_Number() Over (Partition by STAC.STUDENT_ACAD_CRED_ID
Order by STAC.STC_START_DATE) As RowId
FROM STUDENT_ACAD_CRED STAC
WHERE STAC.STC_VERIFIED_GRADE IS NOT NULL) SEL1
ON SEL1.STAC_KEY = STTR.STTR_STUDENT_ACAD_CRED
AND RowId = 1
)

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!

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