SQL Left Outer Join but only pull max date

Question

I noticed variations of this question has been asked but none seem to work for me (or I wasn't looking right). I'm still learning SQL so please bear with me.

I have two tables I'm looking to join. I want all records from Table C and only ones that match from Table P. So I assume a left outer join?

I want all columns from both tables and they will be joined by "PartNo" in Table C and "ItemNo" in Table P.

The tricky part for me is that along with this join, I am looking to only pull the latest "ItemNo" based on date (PDate) in Table P.

Thanks in advance!

Answer 1

This can be done with an outer apply

select *
    from TableC c
    outer apply (
        select top 1 *
            from TableP
                where itemno = c.partno
                order by pdate desc
    ) p