CODEWITHSUNDEEP
sqlsqlite
aadu
aadu

Reputation: 3254

Is it possible to combine these two queries wtih a join in SQLite?

Is it possible to create a JOIN in SQLite between these two queries from different tables...

SELECT EventId as event, 
SUM(CASE WHEN name = 'computer' THEN 1 ELSE 0 END) as computer,
SUM(CASE WHEN name = 'desk' THEN 1 ELSE 0 END) as desk,
SUM(CASE WHEN name = 'chair' THEN 1 ELSE 0 END) as chair 
FROM TagsMSCV 
WHERE name IN ('computer','desk','chair') 
GROUP BY EventId

...and something like...

SELECT COUNT(MicrosoftId) as total FROM Images WHERE Images.EventId = TagsMSCV.EventId

...So that the top query goes from producing...

event   computer   desk   chair   
####    ########   ####   #####
etc...

to

event   computer   desk   chair   total
####    ########   ####   #####   #####
etc...

EDIT: The second query I wrote was pseudo code attempting to create the join. The actual query as a normal select looks like this... 

SELECT Count(MicrosoftId) FROM Images WHERE EventId IN ("10003", "10004", etc.) GROUP BY EventId

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I would do the aggregation first and then left join:

SELECT t.EventId as event, 
       SUM(CASE WHEN t.name = 'computer' THEN 1 ELSE 0 END) as computer,
       SUM(CASE WHEN t.name = 'desk' THEN 1 ELSE 0 END) as desk,
       SUM(CASE WHEN t.name = 'chair' THEN 1 ELSE 0 END) as chair,
       i.total
FROM TagsMSCV t LEFT JOIN
     (SELECT i.eventId, COUNT(MicrosoftId) as total
      FROM Images i
      GROUP BY i.eventId
     ) i
     ON i.EventId = t.EventId 
WHERE t.name IN ('computer', 'desk', 'chair') 
GROUP BY t.EventId, i.total;

By doing the aggregation first, you prevent double counting.

Upvotes: 2

Related Questions