1

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!

Rosadocc
  • 25
  • 3
  • A left join is what you want, but you will need to join table C with a subquery on table P with a group by PartNo clause to get the table you need to join on. You will need to provide more details (database type), etc... if you want a more precise solution. – ozborn Mar 20 '17 at 19:53
  • These are two tables in a SQL Server. I am trying to do the join on Tableau Desktop using the Custom SQL feature – Rosadocc Mar 20 '17 at 19:59
  • I don't have access to SQL server here, but a left join with partition by is probably the way to go if you don't want aggregate values from Table P. – ozborn Mar 20 '17 at 20:15

1 Answers1

2

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
KindaTechy
  • 1,041
  • 9
  • 25