在主表中选择没有联接子表的行。

I have two tables: result (master) and confirmation(sub):

我有两个表:result (master)和确认(sub):

CREATE TABLE result
(
  result_id serial NOT NULL
  CONSTRAINT result_pkey PRIMARY KEY (result_id)
)

CREATE TABLE confirmation
(
  confirmation_id serial NOT NULL,
  result_id integer
  CONSTRAINT confirmation_pkey PRIMARY KEY (confirmation_id),
  CONSTRAINT confirmation_result_id_fkey FOREIGN KEY (result_id)
      REFERENCES result (result_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

I'm able to find all results having a confirmation:

我能找到所有的结果得到确认:

SELECT 
result.result_id,
confirmation.confirmation_id
FROM Result
JOIN confirmation ON confirmation.result_id = result.result_id

But I need it vice-versa - all results still without a single confirmation.

但我需要反之亦然——所有的结果仍然没有一个确认。

1 个解决方案

#1

SELECT * FROM result WHERE NOT EXISTS
(SELECT 1 FROM confirmation WHERE confirmation.result_id = result.result_id)