Selecting a single value from a table

Question

I Have a slightly tricky issue to resolve, Let's say I have a table that looks like this...

 IDn          Val1          Val2
 333XX        SomeVal       SomeVal
 B7B7B7       SomeVal3      SomeVal3

I'm trying to JOIN another table, and surface a new field from that table. 2nd table looks like this:

  ID          Code      Type
  123         333XX     X1
  123         333XX     X2
  123         ORE22     X3
  555         B7B7B7    Y4
  555         B7B7B7    Y5
  555         B7B7B7    Y6
  555         B6B6B6    Y8
  555         848297    Y9

I know this table might look confusing, but generally speaking the ID value, can correspond to many different CODE value. The type value can vary and are not very consistent

So if we look back at my Initial table, I'm trying to add a new Column called ID, and I would want to look like this:

EXPECTED OUTPUT:

 IDn       ID         Val1          Val2
 333XX     123        SomeVal       SomeVal
 B7B7B7    555        SomeVal3      SomeVal3

If I do:

 Select t1.Idn, t2.ID from table1 t1 JOIN table2 t2 on t1.Idn = t2.Code

I get lots of duplicates, however I want to retain the number of rows in my first table, and only surface the ID column from the second table

Answer 1

Have you tried using DISTINCT key word?, or Group By ?

Select distinct t1.Idn, t2.ID, t1.val1, t1.val2 
    from table1 t1 
        JOIN table2 t2 on t1.Idn = t2.Code

Select t1.Idn, t2.ID, t1.val1, t1.val2 
    from table1 t1 
        JOIN table2 t2 on t1.Idn = t2.Code
    group by t1.Idn, t2.ID, t1.val1, t1.val2