Left outer join only with the first line

Question

I have a request 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 work great for me, as long as each product is assigned to only one category. But if a product is assigned to many categories, it returns all possible combinations.

Is it possible to select only the first one, and if the product does not have any category, the link should be returned using category_id = NULL

Answer 1

An easy way is to use an external application to have a correlated connection and make this request 1 on top. This way you can access all the columns of the corresponding category entry. As an example, I add a category name:

 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; 

Answer 2

You can use GROUP BY to accomplish this together with the Unit , most likely MIN or MAX .

Depending on which category identifier you prefer in your result, you can choose the minimum.

 SELECT S.product_id, S.link, MIN(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 GROUP BY S.product_id, S.link 

Or a maximum.

 SELECT S.product_id, S.link, MAX(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 GROUP BY S.product_id, S.link 

Answer 3

Alternative solution using subquery:

 SELECT S.product_id, S.link, ( SELECT C.id FROM Categories C WHERE C.product_id = P.id AND ROW_NUMBER() OVER(ORDER BY /* your sort option goes here*/ ) = 1 ) AS category_id FROM Products P INNER JOIN SEO S ON S.product_id = P.id AND P.product_type = 1 WHERE P.active = 1