CODEWITHSUNDEEP
sqlsqlite
Alan2
Alan2

Reputation: 24572

How can I join two tables and get a count of the number of times the joins match if there might not be a corresponding entry in one table?

In SQLite I have two tables:

public class DeckSource {
   [PrimaryKey, NotNull]
   public string DeckGuid      { get; set; }
   public string Type        { get; set; }
   public string Name { get; set; }
}

and 

public class Deck {
   [PrimaryKey, NotNull]
   public long IdEs { get; set; }
   public string DeckGuid      { get; set; }
   public string Type        { get; set; }
   public string Name { get; set; }
}

My application has an add button which when pressed takes the data from a row in DeckSource and populates Deck. So for every DeckSource there can be multiple decks. I would like to get a report from DeckSource that shows how many times each Deck has been added to Deck.

If the DeckSource table looks like this:

DeckGuid   Name
abc        abcrow
def        deform
ghi        ghirow

and Deck looks like this:

IdEs    DeckGuid    Name
1       abc         abcrow
2       abc         abcrow
3       abc         abcrow
4       chi         ghirow

How can I create a report that shows:

DeckGuid   Name     Qty
abc        abcrow   3
def        defrow   0
ghi        ghirow   1

Upvotes: 1

Views: 46

Answers (1)

Nissus
Nissus

Reputation: 316

Have you tried this?

select decksource.deckguid,decksource.name,count(*)
from decksource 
left join deck 
on decksource.deckguid = deck.deckguid
group by decksource.deckguid

Upvotes: 1

Related Questions