select a.name1, b.name2 from table a join table b on a.id = b.id

enter image description here

How, regardless of the value of the name2 field, to return only 1 record for each product?

there is no distinct name2 not repeated.

where b.name2 = 0 not, because The value of the field is not known. It can be any value.

  • the simplest way is: select a.name1, (select top 1 name2 from table b where b.id = a.id) from table a - nick_n_a

1 answer 1

 select a.name1, MIN(b.name2) AS name2 from table a join table b on a.id = b.id group by a.name1

Instead of MIN you can provide other logic for choosing the name2 value.
If the logic does not fit in the aggregate functions - you can use CROSS APPLY :

 select a.name1, b.name2 AS name2 from table a cross apply (select top 1 name2 from table b where a.id=b.id) b
  • one
    Only not outer apply , but cross apply , since in the original join query, not left join . - i-one
  • @ i-one, I agree. - Zufir