Return first instance in a join

[ghbeers]

I have the following join, but it returns multiple instances because there are some P.ID with multiple valid instances.

But I want only the first valid instance from the first of the below joins. I've tried several methods using TOP, SELECT 1, etc., including creating a temporary table and then joining its contents. But I have had no success.

I will be very appreciative if someone can provide me with the correct syntax. Thank you.

SELECT a bunch of stuff
FROM PERSON
LEFT JOIN STPR_DATES SD
ON SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) = P.ID
AND (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1

and then referenced the results of the above join in the following join and so on:

LEFT JOIN ACAD_PROGRAMS AP1
ON dbo.FIELD(SD.STUDENT_PROGRAMS_ID,'*',2) = AP1.ACAD_PROGRAMS_ID

After several other joins:

WHERE ... select criteria for P.ID

 

[johnherman]

Have you tried the DISTINCT keyword?

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[TamarGranor]

The answer is to use a subquery that picks out whatever you mean by the "first valid instance". For example, if you mean earliest, you'd add something like this to the WHERE clause in your first query:

AND SD.STPR_START_DATE =
(SELECT MIN(SD2.STPR_START_DATE) FROM STPR_DATES SD2
WHERE SUBSTRING(SD2.STUDENT_PROGRAMS_ID,1,7) = P.ID)

If you have two records with the same start date, you'd still get two results for that person.

Tamar

 

[ghbeers]

The previous responses will not get me what I need. The date is not important, other than what is in the selection on SD.STPR_END_DATE. I just want the first record I find that matches what is in the ON clause. DISTINCT won't work and getting the minimum DATE is not applicable. I want something like using TOP 1 or EXISTS or something - I think...

LEFT JOIN STPR_DATES SD
ON SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) = P.ID
AND (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1

Once I have the single record from the above join I then want to use the corresponding SD.STUDENT_PROGRAMS_ID in the join that follows.

LEFT JOIN ACAD_PROGRAMS AP1
ON dbo.FIELD(SD.STUDENT_PROGRAMS_ID,'*',2) = AP1.ACAD_PROGRAMS_ID

 

[SkipVought]

How do you defined the first instance?

 

[ghbeers]

I define the first instance as the first record of many I find where

SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) = P.ID
and
SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE

 

[SkipVought]

Does that not return multiple rows?

 

[ghbeers]

Yes it is as shown in my original question, but I want only the first one that matches.

 

[SkipVought]

Then what logic defines the first row in that resultset!

You realize that there is no implicit row order.

 

[ghbeers]

Currently this syntax is returning all rows in the SD table where the 3 conditions are met.

SELECT a bunch of columns
...
FROM PERSON P
LEFT JOIN STPR_DATES SD
ON SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) = P.ID
AND (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1

Then, currently, all records in the AP1 table are joined on a row from the table when the following condition is met.

LEFT JOIN ACAD_PROGRAMS AP1
ON dbo.FIELD(SD.STUDENT_PROGRAMS_ID,'*',2) = AP1.ACAD_PROGRAMS_ID

I just want the first row found in the result set of the first join, not caring about order. I could order them, but it is possible that all of the END_DATE are null. I really just want the correct syntax because everything I've tried has not worked.

Perhaps I am not understanding your question.

 

[SkipVought]

So you don't really care which row you return, just one of the rows in the resultset?

 

[ghbeers]

Yes,that is correct.

 

[johnherman]

how about DISTINCT (
SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) = P.ID
and
SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE
)

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Olaf Doschke]

If a join returns more than 1 row matching, there is no way to let it only return the first match, so you need to join a table or subquery only having the TOP 1 record of the matching records. And for TOP 1 to work, you need to define an order, so you're back at what Tamar suggested earlier. You can't go around that, there is no such SQL join.

The easiest then is to not join but simply take TOP 1 from each of the child tables in further results you show side by side in your frontend. Don't try to force it in one wide result, take 2 or three results and display the first record of each one. Would that work out?

Or how about the principle of deferred loading of data? You display the persons list, if a users picks a person you select the detail data for that person.

Bye, Olaf.

 

[ghbeers]

Unfortunately DISTINCT will not work because there will be multiple rows in the SD table key when the criteria is matched and each key will have a different value. For example a student may have rows in the table with keys looking like 111111*ABC and 111111*XYZ when the end date is null. I only want the first one found, such as 111111*ABC. I don't care which one, I just want one, any one. I was hoping I could use TOP 1 somehow, but every syntax I tried using TOP, SQL did not like.

 

[johnherman]

I think subquery is the way to go:

SELECT a bunch of stuff
FROM PERSON P
WHERE P.ID in (
select distinct SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7)
from STPR_DATES SD
where (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1
)

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

try this:

SELECT a bunch of columns 
...
FROM PERSON P
LEFT JOIN (Select *,
                  SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) As SP_ID,
                  Row_Number() Over (Partition By SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) Order By STUDENT_PROGRAMS_ID) As RowId
           From   STPR_DATES
           Where  (STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
                  AND SD.POS = 1
		   ) SD
ON SD.SP_ID = P.ID
AND RowId = 1

I have not tested this, so there could syntax errors. However, this should give you just one match for each student. If this works, and you would like me to explain it, just ask.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ghbeers]

I corrected the syntax that gmmastros submitted and it appears it is working correctly. I still have to do a little detail testing on the numbers the report is generating, but it reduced the total counts of each program's students to about what I think they should be.

Thank you so much to all of you for assisting me with my challenging problem.

LEFT JOIN (SELECT dbo.FIELD(SD.STUDENT_PROGRAMS_ID.*.2) AS SP_PROG,
SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) As SP_ID,
Row_Number() Over (Partition By SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) Order By SD.STUDENT_PROGRAMS_ID) As RowId
From STPR_DATES SD
Where (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1
) SD1
ON SD1.SP_ID = P.ID
AND RowId = 1

 

[ghbeers]

Just want to let everyone who responded to my post, that the syntax in my previous reply generated a 100% correct result set. Thank you all again.

 

[ghbeers]

I just wanted to correct the join I posted earlier. It contained some typos. This is what it should have been.

LEFT JOIN (SELECT dbo.FIELD (SD.STUDENT_PROGRAMS_ID,'*',2) AS SP_PROG,
SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) As SP_ID,
Row_Number() Over (Partition By SUBSTRING(SD.STUDENT_PROGRAMS_ID,1,7) Order By SD.STUDENT_PROGRAMS_ID) As RowId
From STPR_DATES SD
Where (SD.STPR_END_DATE IS NULL OR SD.STPR_END_DATE >= @REPORTRUNDATE)
AND SD.POS = 1
AND dbo.STPR_ACAD_LEVEL (SD.STUDENT_PROGRAMS_ID) = 'CC'
) SD1
ON SD1.SP_ID = P.ID
AND RowId = 1