0

I'm not a SQL expert and although this seemed like a simple enough query, I can't figure out how to do it...I've tried everything I know, have googled and tried things I didn't know but I just can't get a clean output. I'm sure this must have been asked before but I have failed to find the answer. Hoping someone can put me out of my mental misery...

TABLE 1

TABLE 1

TABLE 2

TABLE 2

What I need to is:

for each acc_nbr/sip_nbr pair in TABLE 1, get the latest sell_dt and using the sell_dt, get the price of the sip_nbr on that date from table 2

So for example, for yyGG10234/xxFF10234, the output should be

OUTPUT

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
keerz
  • 717
  • 1
  • 6
  • 21
  • I removed the incompatible database tags. Please tag only with the database you are really using. – Gordon Linoff Feb 07 '19 at 17:07
  • Also, please add any required data/text/code etc directly into the question. Please [edit] your question. Thanks. – grooveplex Feb 07 '19 at 17:08
  • https://stackoverflow.com/questions/16597660/sql-join-on-multiple-columns-in-same-tables asked before... That along with this... https://www.w3schools.com/sql/sql_join.asp should make your task easy – JGFMK Feb 07 '19 at 17:19
  • @Gordon thanks. I didn't think I had tagged any DBs but will be more careful – keerz Feb 07 '19 at 18:08
  • Thanks. I didn't put code in because I had tried so many things but understood. I've marked answer below – keerz Feb 07 '19 at 18:10

1 Answers1

0

Use a subquery to get max date for each combo of acc_nbr and sip_nbr and then join the other table against that subquery

SELECT acc_nbr, t1.sip_nbr, max_sell, price
FROM (SELECT sip_nbr, acc_nbr, MAX(sell_dt) as max_sell
      FROM table1 
      GROUP BY sip_nbr, acc_nbr) t1 
LEFT JOIN table2 t2 ON t1.sip_nbr = t2.sip_nbr AND max_sell = t2.pricedate

I have used a left join to include all rows from table 1 (the subquery) but if you only want those that has a price date for the date then remove LEFT.

Joakim Danielson
  • 43,251
  • 5
  • 22
  • 52