CODEWITHSUNDEEP
sqlsqlitejoin
Alan2
Alan2

Reputation: 24562

How do I do a multi-table SQL join with grouping

I have three tables that are represented here by objects and that convert to SQLite tables. The CategoryGroup table has multiple Categories and each Category has multiple Phrases.

public class CategoryGroup
{
    [PrimaryKey, NotNull]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Selected { get; set; }
}

public class Category : ICategory
{
    [PrimaryKey, NotNull]
    public int Id { get; set; }
    public int CategoryGroupId { get; set; }
    public string Name { get; set; }
    public bool Selected { get; set; }
}

public class Phrase : IPhrase
{
    public int CategoryId { get; set; }
    [PrimaryKey, NotNull]
    public string PhraseId { get; set; }
    public string English { get; set; }
    public string Romaji { get; set; }
    public string Kana { get; set; }
    public string Kanji { get; set; }
    public int Modified { get; set; }
}

What I would like to do is to join these three tables and group them by CategoryGroup and then report on the Selected count.

I created this SQL but I am not sure about the three table join. 

SELECT CG.Id, CG.Name, COUNT(*) AS PhraseCount
FROM CategoryGroup AS CG
FROM Category AS C
JOIN CG.Id = P.CategoryId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id
ORDER BY CG.Name

Can someone give me advice on how I can do the join?

Upvotes: 1

Views: 32

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You've got the syntax wrong. You'd select from one table, join another on some criteria so that records match, then join the third on some other criteria linking this table.

SELECT CG.Id, CG.Name, COUNT(*) AS PhraseCount
FROM CategoryGroup AS CG
JOIN Category AS C ON C.CategoryGroupId = CG.Id
JOIN Phrase AS P ON P.CategoryId = C.Id
GROUP BY CG.Id
ORDER BY CG.Name;

You may have to GROUP BY CG.Id, CG.Name instead. This is not necessary by the SQL Standard, because the category group name is functionally dependent on the category group ID, but some DBMS demand this though (because they find it too hard to determine functional dependence). I don't know about SQLite in this respect.

Upvotes: 1

Related Questions