Using COUNT in INNER JOIN in SQLite (to count absent records)

In SQLite, I have a table named Items:

| id | name  |
|----|-------|
|  1 | ..    |
|  2 | ..    |
|  3 | ..    |
|  4 | ..    |
|  5 | ..    |
|  6 | ..    |

Each item might have tags associated with it. Tags are stored in another table, and have their own IDs. The association of items and tags is stored in a third table called Associations:

| itemID | tagID  |
|--------|--------|
|      1 |      1 |
|      1 |      2 |
|      1 |      3 |
|      2 |      1 |
|      3 |      4 |
|      3 |      5 |
|      4 |      5 |

As you can see, item 1 has 3 tags, items 2 and 4 have 1 tag each, and item 3 has 2 tags. Items 5 and 6 have no tags, so their IDs do not appear in the Associations table.

Now, I want to retrieve IDs of items having less than, say, 2 tags, including zero tags. If it were only about the Associations table, the task is easy:

SELECT itemID FROM Associations GROUP BY itemID HAVING COUNT(tagID) < 3;

But this will not return IDs of items which do not have tags, as they are absent in the Associations table. So I guess I have to use JOIN somehow. I tried

SELECT id FROM Items INNER JOIN Associations ON Associations.itemID=Items.id GROUP BY itemID HAVING COUNT(Associations.tagID) < 3;

But this seems to do the same as the previous query. Help is appreciated 🙂

EDIT: So, the answer is just to replace INNER JOIN with LEFT JOIN and GROUP BY itemID with GROUP BY Items.id.

QUESTION EXTENDED: Now, to tell the secret, the tags (apart from having IDs) fall into different categories, say, red, green and blue (so, e.g., red tag with id 5 and green tag with id 5 are in fact different tags).

| itemID | tagID  |tagType|
|--------|--------|-------|
|      1 |      1 | red   |
|      1 |      2 | red   |
|      1 |      3 | blue  |
|      2 |      1 | green |
|      3 |      4 | blue  |
|      3 |      5 | red   |
|      4 |      5 | blue  |

So I need to retrieve IDs of items which have less than N tags of specific type. I thought I could easily solve this by using the Yogesh’s answer by adding the WHERE clause like this:

SELECT i.id AS itemID  
FROM Items i LEFT JOIN 
 Associations a 
 ON a.itemID = i.id
WHERE a.tagType='red'
GROUP BY i.id
HAVING COUNT(a.tagID) < 3;

But this again fails to return items which do not have tags at all, because they are now filtered out by the WHERE condition. I think I need something along these lines:

SELECT i.id AS itemID  
FROM Items i LEFT JOIN 
 Associations a 
 ON a.itemID = i.id
GROUP BY i.id
HAVING COUNT(a.tagID WHERE a.tagType='red') < 3;

But this does not seem to be a valid statement. Help appreciated again; I might “reaccept” the answer.

Answer

Do LEFT JOIN rather than INNER JOIN :

SELECT i.id AS itemID  
FROM Items i LEFT JOIN 
     Associations a 
     ON a.itemID = i.id 
GROUP BY i.id
HAVING COUNT(a.tagID) < 3;

Advertisement

Answer