How to get non-existant values as NULL or 0?

Pól Ua Laoínecháin <linehanp@xxxxxx> · Sat, 29 Jun 2019 17:20:56 +0100

I'm in a bit of a quandary. I'm trying to figure out how to have
non-existent values appear as NULL (or using COALESCE, as 0).

I have several tables (DDL and DML shown at the bottom of this post -
and there is a fiddle available here:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=171d207d25981e0db15791e1684de802

The crux of the problem is that there is a joining table location_job

CREATE TABLE location_job
(
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  &c... - see fiddle or below
);

with entries as follows:

INSERT INTO location_job
VALUES
(1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing
value (1, 1, 10)
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);

Now the lj_job from the missing value corresponds to a job_type of Unknown.

So, performing this query:

SELECT
  tl.loc_key, tl.loc_coords,
  lj.lj_loc, lj.lj_job, lj.lj_percent,
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;

Gives me the results:

loc_keyloc_coordslj_loclj_joblj_percentjob_idjob_type
1coord_112102Unemployed
1coord_113103Blue collar
1coord_114104White collar

Notice that (as expected) there is no result for job_type Unknown for
location 1 - it's the missing record - all fine and dandy!

However, what I want to do is to include a record in my resultset that
has NULL (or 0) for job_type Unknown.

You will be able to see from the fiddle that I have tried varying the
order of the joins and the join types (LEFT OUTER, RIGHT OUTER, FULL
OUTER), all to no avail.

My question boils down to how do I get the first line of my resultset to be

1 coord_1 1 1 0 1 Unknown -- the 0 corresponds to the percent of
Unknowns in the location coord_1.

I would be grateful for an explanation of any answer as I'm trying to
learn SQL and this really has me stumped.

Should you require any further information, please don't hesitate to
let me know,

TIA,

Pól...

=========== DDL and DML ===============

CREATE TABLE crime
(
  cr_rating VARCHAR (25) PRIMARY KEY
);

INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');

CREATE TABLE weather
(
  weather_type VARCHAR (50) PRIMARY KEY
);

INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'),
('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');

CREATE TABLE job (job_id SERIAL PRIMARY KEY, job_type VARCHAR (30) NOT NULL);

INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue
collar'), ('White collar'), ('Manager'), ('Self-employed');

CREATE TABLE the_location
(

  loc_key SERIAL PRIMARY KEY,
  loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in
your system
  loc_status INTEGER CHECK (loc_status IN (0,1)),
  loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10),
  loc_crime VARCHAR (25) NOT NULL,
  -- could use a CHECK CONSTRAINT, but use a lookup table instead
  -- note also that the types should match exactly
  loc_weather VARCHAR (50) NOT NULL,
  -- location_rating_tourism, -- same lookup table idea here (Good,
Medium, Poor, Death-zone)
  -- location_rating_income, -- list of social classes table
  -- location_rating_jobs
  CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
  CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES
weather (weather_type)
);

INSERT INTO the_location
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather)
VALUES
('coord_1', 0, 7, 'Medium', 'Rainy'),
('coord_2', 1, 7, 'High', 'Mild');

CREATE TABLE location_job
(
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  PRIMARY KEY (lj_loc, lj_job),
  CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
  CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);

INSERT INTO location_job
VALUES
(1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing
value (1, 1, 10)
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);

-- and my first query - see fiddle for others

SELECT
  tl.loc_key, tl.loc_coords,
  lj.lj_loc, lj.lj_job, lj.lj_percent,
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;