seo Question posted in
Some basic information to get you started with this can be found here.

Left outer join with only first row – SEO

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

Tags:

3

Answers


  1. 0

    You can use a GROUP BY to accomplish this along with an Aggregate function, most likely MIN or MAX.

    Depending on which Category Id you prefer in your result you could select 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 the 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
    Login or Signup to reply.
  2. 0

    Alternate 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
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search