CODEWITHSUNDEEP
sqlsqlitesubquery
Echilon
Echilon

Reputation: 10244

SQL Subquery Optimization

I have two tables, Categories and RecipeCategories

**Categories**
CategoryID varchar (Primary Key)
Name varchar

**RecipeCategories**
RecipeID varchar
CategoryID varchar
Composite primary key

I'm searching for a way to return CategoryID and Name for all categories, plus whether or not the recipe is actually in that category. I could use something like

SELECT c.CategoryID, c.Name, 
         (SELECT COUNT(*) 
          FROM RecipeCategories 
          WHERE RecipeID = @recipeId AND CategoryID = c.CategoryID))
FROM Categories c

But I don't think that would scale if the table gets too big.

Upvotes: 2

Views: 135

Answers (2)

Espen Burud
Espen Burud

Reputation: 1881

You can use the example below, but to make this solution to be scalable in large environments you need to indexes RecipeID.

SELECT c.CategoryID, c.Name, count(rc.Composite) FROM Categories c
LEFT JOIN RecipeCategories rc ON c.CategoryID = rc.CategoryID
WHERE RecipeID = @recipeId
GROUP BY c.CategoryID, c.Name;

Upvotes: 0

DavidEG
DavidEG

Reputation: 5947

SELECT c.CategoryID
     , c.Name
     , Case When (r.RecipeID is null) Then 'No' Else 'Yes' End
  FROM Categories c
  left join RecipeCategories r on r.CategoryID = c.CategoryID
                              and r.RecipeID = @recipeId

Upvotes: 1

Related Questions