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

 

[Olaf Doschke]

OK, solution noticed.

As said earlier, you have to give an order to get a top 1 record, that also doesn't differ, when using row_number(), the ORDER BY sd.studient_programs_id is not optional to number the rows, they have to be in an order.

I wonder about your statement "it reduced the total counts of each program's students to about what I think they should be". Somewhat logic, if you expect to get the student count and no multiplicator, but then you join some detail data via LEFT join, which would render the report result quite random in what it displays in such details. If you'd do an INNER join I could understand, that you use the joined data to filter students having some detail, eg having participated in some program, but as you do a LEFT join you get any student, no matter if there is such detail data or not.

It's something that will be irrelevant after an hour or so, but I don't get the logic of joining any detail data, if you would display something in the report, it should rather be aggregated detail data or no detail data, but not random detail data, and if you want to filter students having some detail data related to them in child tables, then INNER join and not LEFT join.

Bye, Olaf.

 

[Olaf Doschke]

Just one more notice:

To determine, whether there is some detail (STPR_DATES) for some main data (PERSON) you'd not join the detail data, but do a WHERE EXISTS subquery, because then it won't matter, whether just one or many detail records exists, only no existing detail record would then remove the main data record from the overall result.

Bye, Olaf.