Left outer join with only first row

Question

I have a query something like

SELECT S.product_id, S.link, C.id AS category_id
FROM Products P
INNER JOIN SEO S ON S.product_id = P.id AND P.product_type = 1
LEFT OUTER JOIN Categories C ON c.product_id = P.id
WHERE P.active = 1

I works fine for me as long as each product has assigned to only one category. But if a product is assigned to many categories it returns all possible combinations.

Can I only select the first one and if a product don't have any category the link should still be returned with category_id = NULL

Answer 1

An easy way is to use outer apply, so as to have a correlated join, and make that a top 1 query. Thus you are able to access all columns of the category record in question. I'm adding a category name here as an example:

select s.product_id, s.link, c.id as category_id, c.name as category_name
from products p
inner join seo s on s.product_id = p.id 
outer apply
(
  select top 1 * 
  from categories cat
  where cat.product_id = p.id
  order by cat.id
) c
where p.active = 1
and p.product_type = 1;