CODEWITHSUNDEEP
sqlsql-servert-sql
Madhatter5501
Madhatter5501

Reputation: 163

Is there a more efficient way of writing this stored procedure?

I am new to sql, is there a better way or more efficient way of writing the following query I wrote. I am trying to pull all the recipe categories by recipe id, if their is a record for that cateogy for the recipe id in the junction table, it should return true, otherwise return false.

here is a screenshot of the expected output for one recipe id, you see how some have IsTagged True, others are False

ScreenShot

SELECT Name AS TagName, Id AS TagID, 'false' AS IsTagged
FROM dbo.Category

EXCEPT 

SELECT category.Name AS TagName, category.ID AS TagID, 'false' AS IsTagged
FROM dbo.Category AS category
LEFT OUTER JOIN dbo.RecipeCategoryJunction AS junction
    ON junction.CategoryID = category.Id
LEFT OUTER JOIN dbo.Recipes AS recipe
    ON recipe.Id = junction.RecipeId
WHERE recipe.Id = 1019

UNION

SELECT category.Name AS TagName, category.ID AS TagID, 'true' AS IsTagged
FROM dbo.Category AS category
LEFT OUTER JOIN dbo.RecipeCategoryJunction AS junction
    ON junction.CategoryID = category.Id
LEFT OUTER JOIN dbo.Recipes AS recipe
    ON recipe.Id = junction.RecipeId
WHERE recipe.Id = 1019

Upvotes: 0

Views: 85

Answers (1)

radar
radar

Reputation: 13425

you can do it with a left join alone.

SELECT C.Name AS TagName, 
       C.Id AS TagID,
       (CASE WHEN junction.CategoryID is NULL then 'False' Else 'True') AS IsTagged
FROM dbo.Category C
LEFT JOIN dbo.RecipeCategoryJunction AS junction
    ON junction.CategoryID = category.Id
    junction.RecipeId =1019

Upvotes: 1

Related Questions