join 4 tables no dupes

[mcnewsxp]

i have a main student table. i need to join to an insurance table where the insurance type is not type 8. no problem. i need to join to two other tables that are one to many. each of these tables has a date column i can use to get the most current record. i figured out how to get one of them, but getting both is getting murky for me. i could use some help.
tia,
mcnewsxp

[Erland Sommarskog]

Post by mcnewsxp
i have a main student table. i need to join to an insurance table where
the insurance type is not type 8. no problem. i need to join to two
other tables that are one to many. each of these tables has a date
column i can use to get the most current record. i figured out how to
get one of them, but getting both is getting murky for me. i could use
some help.

For this kind of problem, I recommend that you incldue:

1) CREATE TABLE statements for your tables (preferably simplified to
what is pertinent for the problem.)
2) INSERT statements with sample data.
3) The desired result given the sample.
4) A short description of the business rules.
5) Which version of SQL Server you are using.

I like to add that you are under no obligation to provide all this
information, but the more accurate your description is, the more likely
that you get a usable answer. With the tiny amount of give above, I am
not going to try even a wild guess.

[mcnewsxp]

this is a good enough example of the main table and the two tables i need to join. never mind about the 4th table.
i only need the most resent sign_date from the records in tableb and tablec.

STUDENTS
-----------
STUDENT_ID, NAME, ADDRESS
101, joe, 123 street
102, jill, 345 avenue
111, kipper, 678 lane

TABLEB
---------
STUDENT_ID, SIGN_DATE
101, 02.12.2013
101, 01.11.2013
101, 12.12.2012
102, 02.14.2013
102, 01.17.2013
102, 12.22.2012
111, 02.21.2013
111, 01.10.2013
111, 12.10.2012
111, 11.15.2012

TABLEC
---------
STUDENT_ID, SIGN_DATE
101, 02.22.2013
101, 01.21.2013
101, 12.11.2012
102, 02.13.2013
102, 01.19.2013
102, 12.12.2012
111, 02.22.2013
111, 01.11.2013
111, 12.11.2012
111, 11.11.2012

[Erland Sommarskog]

Post by mcnewsxp
this is a good enough example of the main table and the two tables i
need to join. never mind about the 4th table. i only need the most
resent sign_date from the records in tableb and tablec.

To get the most recent row out of a table, use a CTE with row_number
and then filer on rowno = 1:


; WITH numbered_table AS (
SELECT col1, col2, ...
rowno = row_number() OVER (PARTITION BY keycol1, keycol2, ...
ORDER BY datecol DESC)
FROM tbl
)
SELECT col1, col2, ...
FROM numbered_table
WHERE rowno = 1

Once you have this going, the joins are a breeze.