CODEWITHSUNDEEP
androidsqlitejoin
png
png

Reputation: 4408

Android : sqlite join result

I have a table which has one column's name and guestof .

I want to return a cursor to get three values : name guestOf and guestcount where guestcount is how many rows has "name" as the value of guestOf.

How can combine this internal query and return the result. Trying something like this, can it be done without inner query for better performance

SELECT name, guestCount from Table
   as A outer join (SELECT guestOf , count(*) 
   AS GuestCount FROM Table group by guestOf) 
   as GuestCounts WHERE  A.id = GuestCounts.guestOf

Upvotes: 0

Views: 98

Answers (1)

Michael Dodd
Michael Dodd

Reputation: 10270

Give this code a go. I think it matches what you're looking for:

SELECT g.name, g.guestof, 
    (SELECT COUNT(guestof) 
         FROM guests AS c
         WHERE g.name == c.guestof
     ) as guestcount
FROM guests AS g;

And as an example: SQLFiddle

Upvotes: 2

Related Questions