CODEWITHSUNDEEP
sqljoinssms-2012
Koosh
Koosh

Reputation: 896

Selecting a single value from a table

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

Upvotes: 0

Views: 1546

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Probably the most performant method uses OUTER APPLY:

Select t1.Idn, t2.ID
from table1 t1 outer apply
     (select top (1) t2.*
      from table2 t2 
      where t1.Idn = t2.Code
     ) t2;

This can make good use of an index on table2(code). You can add an ORDER BY if you want a particular id -- such as the newest, oldest, etc.

Upvotes: 1

Mauricio Atanache
Mauricio Atanache

Reputation: 2581

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

Upvotes: 2

Related Questions