CODEWITHSUNDEEP
sqlite
Lukasz 'Severiaan' Grela
Lukasz 'Severiaan' Grela

Reputation: 6258

list all entries and count occurrences in other table

I have DB with few tables, 2 of which are defined as:

CREATE TABLE [projects] (
  [id]  INTEGER         NOT NULL PRIMARY KEY,
  [code]    VARCHAR(128)    UNIQUE NOT NULL COLLATE NOCASE,
  [name]    VARCHAR(128)    DEFAULT "Not Set" NOT NULL COLLATE NOCASE
)

CREATE TABLE [tasks] (
  [id]          INTEGER  NOT NULL PRIMARY KEY,
  [project_id]  INTEGER  NOT NULL,
  [notes]       VARCHAR(512) DEFAULT 'Not Set' NOT NULL COLLATE NOCASE,
  [start]       DATE  NOT NULL,
  [finish]      DATE  NOT NULL,
  [rate_id]         INTEGER DEFAULT 1 NOT NULL
)

I have this query that returns list of projects details with the number of use:

SELECT p.*, count(t.project_id) as length
FROM projects as p, tasks as t
WHERE p.id=t.project_id
GROUP BY p.code
ORDER BY length DESC;

but as I have found out it will return list smaller than projects if project id wasn't used (no task with given project id).

Is there a way to create a full list of projects in the same format to my current query and adding length but to include ALL projects and for those with no tasks length will have 0?

Upvotes: 1

Views: 260

Answers (1)

CL.
CL.

Reputation: 180060

To also get projects without tasks, use a left outer join. The ifnull function converts any NULL to zero:

SELECT p.*, IFNULL(COUNT(t.project_id), 0) AS length
FROM projects AS p
LEFT JOIN tasks AS t ON p.id = t.project_id
GROUP BY p.code
ORDER BY length DESC

Upvotes: 1

Related Questions