0

I am trying to lookup values from Table 1 to Table 2 based on Col1 in Table 1.

The catch is that Table 1 has duplicate values (for example, A is repeated 3 times) but I don't want to duplicate the returned value from Table 2.

How can this be done through either excel or sql (e.g. LEFT JOIN)?

image of expected result

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
biz_w
  • 1
  • How would you, in your example, link the number to a color? – Kars Mar 23 '19 at 16:39
  • Why is there 0 in Col3 instead of 100 or 150? When do you want to get 0 for this column? – Pavel Smirnov Mar 23 '19 at 16:40
  • This can't be done using one SQL statement you have to use DML statement as well – Kedar Limaye Mar 23 '19 at 16:42
  • I should clarify that col2 (color information) isn't relevant to the lookup. I am just trying to lookup based on col1. So if we did a regular vlookup in excel, every row where col1 = A would have 100 value and every row where col1 = B would return 150. I was wondering if it's possible to do a lookup based on unique values in col1 (e.g. 100 shows up once for A rows, 150 shows up once for B rows). Hence the 0 values for col3. Hope this explains it better. – biz_w Mar 23 '19 at 21:18

1 Answers1

0

What SQL are you using? Are you familiar with CTE and partition? Have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/597b876e-eb00-4013-a613-97c377408668/rownumber-and-cte?forum=transactsql and here: (answer and 2nd comment): Select the first instance of a record

You can use those ideas to create another field that tells you whether the row is the first, 2nd , 3rd etc occurrence of Col1. Eg you'd have something like 1 B Red 150 2 B Red 150

and you can then update col3 to be zero where this new field is not 1.

EDIT: since you asked about Excel: in Excel, sort by whatever criteria you may need (col 1 first, of course). Let's say that Col1 starts (excluding the heading) in cell C2. Set cell B2 =1. Then write this formula in cell B3:

=IF(C3=C2,B2+1,1)

and drag it all the way down. This will count the occurrences of col 1, ie it will tell you which is the first, 2nd etc time a given value appears in col1. You can then use it as as the basis to change the value in other columns.

Also, it is not good practice to have a column where the first cell has a different formula from the others. You can use the same formula nesting another IF and referencing the row, so as to set one formula for the first row and one for the others.

Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112
  • I'm using Teradata SQL. Interesting, I am not familiar with partition. Sounds like it could work. Also, if this can be done in regular excel with some sort of adjusted vlookup or index/match that works for me too. – biz_w Mar 23 '19 at 21:19