Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table projects (id int, slug varchar(10));

CREATE TABLE

insert into projects (id, slug) values (1, 'p3'), (2, 'p4');

INSERT 0 2

create table widgets (id int, code varchar(10), project_id int);

CREATE TABLE

insert into widgets (id, code, project_id) values (10, 'w1', 1);

INSERT 0 1

-- Existing project, existing witget
SELECT p.id, p.slug, COALESCE(w.id, 0) as id, COALESCE(w.code, '') as code
FROM projects p 
LEFT OUTER JOIN widgets w ON p.id = w.project_id 
                         AND w.code = 'w1'
where p.slug = 'p3';

id
slug
id
code

1
p3
10
w1

SELECT 1

-- Existing project, NON-existing witget
SELECT p.id, p.slug, COALESCE(w.id, 0) as id, COALESCE(w.code, '') as code
FROM projects p 
LEFT OUTER JOIN widgets w ON p.id = w.project_id 
                         AND w.code = 'w2'
where p.slug = 'p3';

id
slug
id
code

1
p3
0

SELECT 1

-- NON-Existing project
SELECT p.id, p.slug, COALESCE(w.id, 0) as id, COALESCE(w.code, '') as code
FROM projects p 
LEFT OUTER JOIN widgets w ON p.id = w.project_id 
                         AND w.code = 'w1'
where p.slug = 'missing-project';

id
slug
id
code

SELECT 0