CODEWITHSUNDEEP
sqlsqlite
wp7mobileusers
wp7mobileusers

Reputation: 243

howto realise a query which makes a second query to count the sum?

i have a problem designing a SQLite query, i have two tabels, the first is:

first: id, name

and the second

second: id, first_id, name

now i have entries in first and the entries of second always have a id from first in the first_id field. now i want to get all the names from the first table PLUS the count of entries with the first_id same as the if in the first table.

how to realize such a query?

Upvotes: 0

Views: 47

Answers (2)

Andomar
Andomar

Reputation: 238116

You could join the tables together, and group by on rows in the first table:

select  first.id
,       first.name
,       count(*)
from    first
left join
        second
on      first.id = second.first_id
group by
        first.id
,       first.name

Upvotes: 1

Jacob
Jacob

Reputation: 43229

SELECT first.name, (
 SELECT COUNT(*) 
 FROM second
 WHERE second.first_id=first.id
) as entries
FROM first

Upvotes: 0

Related Questions