user3733648 user3733648 - 1 year ago 91
SQL Question

Left outer join with only first row

I have a query something like

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

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 Source

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,, as category_id, as category_name
from products p
inner join seo s on s.product_id = 
outer apply
  select top 1 * 
  from categories cat
  where cat.product_id =
  order by
) c
where = 1
and p.product_type = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download