CODEWITHSUNDEEP
sqliteandroid-sqlite
Kris B
Kris B

Reputation: 3578

SQLite Count between two tables

I'm not sure if this is possible in SQLite (for an Android app) as it may require and an INNER JOIN or OUTER JOIN which sqlite doea not support.

I'm trying to output the values of a JOIN between two tables that includes the count of fields in one table:

[Table1]
ID  Title
1   "Title 1"
2   "Title 2"
3   "Title 3"

[Table2]
ID PID Title      New
1  1   "Title 1a"  0
2  2   "Title 2a"  1
3  2   "Title 2b"  1
4  3   "Title 3a"  0
5  3   "Title 3b"  0

Desired output:
1  0  "Title 1"
2  2  "Title 2"
3  0  "Title 3"

What I have:

SELECT Table2.pid,count(Table2.pid),Table1.title
FROM Table1
JOIN Table2 
ON Table2.pid = Table1.id
WHERE Table2.new = 1
GROUP BY Table2.pid

Outputs:

2  2  "Title 2"

Upvotes: 0

Views: 85

Answers (2)

forpas
forpas

Reputation: 164089

You can get the value you want by aggregation in a subquery:

select 
  (select count(*) from Table2 t2 where t2.pid = t1.id and t2.New = 1) counter,
  t1.Title
from
  Table1 t1

See the demo

Another solution with group by:

select 
  count(t2.Title) counter, 
  t1.Title 
from Table1 t1
left join (
  select * from Table2 where new = 1
) t2
on t2.pid = t1.id
group by t1.Title

See the demo

Upvotes: 2

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

You can do it with a LEFT JOIN in sqlite. Simply change JOIN to LEFT JOIN. From the doc:

If the join-operator is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that corresponds to no rows at all in the composite dataset (if any). The added rows contain NULL values in the columns that would normally contain values copied from the right-hand input dataset.

It will return null when there are no rows in Table2. (Perhaps you could use a CASE statement to display 0, but I'm not familiar with the interface).

Upvotes: 0

Related Questions