Как запросить две таблицы ссылок в одном запросе?

#sql #postgresql #left-join

#sql #postgresql #левое соединение

Вопрос:

У меня есть две таблицы. device это может иметь blacklisted_device значение. Я хотел бы получить количество устройств, которые включают определенное user_ids и в том же запросе количество blacklisted_devices связанных.

Вот полный sql, чтобы попробовать :

 CREATE TABLE device (
  device_id serial PRIMARY KEY,
  user_id integer,
  updated_at timestamp default current_timestamp
);

CREATE TABLE blacklisted_device (
  blacklisted_id serial PRIMARY KEY,
  device_id integer,
  updated_at timestamp default current_timestamp,
  CONSTRAINT blacklisted_device_device_id_fkey FOREIGN KEY (device_id)
      REFERENCES device (device_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
INSERT INTO device (user_id)
VALUES (1),(2),(2),(7),(88),(99),(102),(106);

INSERT INTO blacklisted_device (device_id)
VALUES (1),(2),(3),(4);

SELECT COUNT(*) AS total_device
FROM device
WHERE user_id IN (7,88,99);

SELECT COUNT(*) AS blacklisted
FROM blacklisted_device
WHERE device_id IN (SELECT device_id FROM device WHERE user_id IN (7,88,99));

Как вы можете видеть в конце, я получаю желаемый результат, но в двух запросах. Как получить это в одном запросе?

total_device: 3, blacklisted: 1

Не стесняйтесь делать какие-либо комментарии по всему SQL, я, вероятно, допустил несколько ошибок.

Спасибо

Ответ №1:

Вам нужен LEFT JOIN :

 SELECT  COUNT(*) AS total_device,
        COUNT(DISTINCT bd.device_id) AS blacklisted
FROM device d
LEFT JOIN blacklisted_device bd
    ON d.device_id = bd.device_id
WHERE d.user_id IN (7,88,99);