CODEWITHSUNDEEP
sqlsqlite
Alan2
Alan2

Reputation: 24562

How can I do an outer join with a group by in SQLite?

I have three tables in SQLite that were created from these classes. I joined these with the SQL below. 

public class Phrase {
    [PrimaryKey, NotNull]
    public string PhraseId { get; set; }
    public int PhraseNum { get; set; }
    public int CategoryId { get; set; }
}
public class Category {
    [PrimaryKey, NotNull]
    public int Id { get; set; }
    public int GroupId { get; set; }
    public string Name { get; set; }
    public bool Selected { get; set; }
}
public class CategoryGroup {
    [PrimaryKey, NotNull]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsToggled { get; set; }
}

So far I have this SQL

SELECT CG.NAME, COUNT(*) AS TotalPhraseCount 
FROM Phrase AS P
JOIN Category AS C ON P.CategoryId = C.Id
JOIN CategoryGroup AS CG ON C.GroupId = CG.Id
WHERE C.Selected = 1
GROUP BY C.GroupId

Which gives me as one of the Categories in Category group has 11 phrases and it's Selected:

CategoryGroupName PhrasesCount
A                 20

However what I need is to see every CategoryGroup row and a 0 if there are no Categories selected in that group. So for example this which includes the CategoryGroup B which has no Categories selected:

CategoryGroupName PhrasesCount
A                 20
B                 0
C                 0

I think I can do this with an outer join but I am not sure how to implement that in SQL.

Each category contains many phrases Each category group contains many categories Each category belongs to just one Category group Each phrase belongs to just one Category

Hope that helps

Here's the output I have from Juan's query. Not sure why there is a 1 in rows B and C

CategoryGroupName PhrasesCount
A                 20
B                 1
C                 1

Upvotes: 0

Views: 51

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

SELECT CG.NAME, COUNT(C.GroupId) AS TotalPhraseCount 
FROM       CategoryGroup AS CG
LEFT JOIN  Category AS C 
       ON  CG.Id = C.GroupId
      AND  C.Selected = 1                         
LEFT JOIN  Phrase AS P 
       ON P.CategoryId = C.Id
GROUP BY CG.NAME

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use left join

SELECT CG.NAME, COUNT(*) AS TotalPhraseCount 
FROM Phrase AS P
left JOIN Category AS C ON P.CategoryId = C.Id
                       and C.Selected = 1
left JOIN CategoryGroup AS CG ON C.GroupId = CG.Id

GROUP BY C.GroupId

Upvotes: 1

Related Questions