Beni Mio Beni Mio - 1 month ago 6
SQL Question

How to query two link tables in the same query?

I have two tables.

device
that can have on
blacklisted_device
. I would like to get the number of device that include specific
user_ids
and in the same request number of
blacklisted_devices
linked.

Here the full sql to try it :

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 (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));


As you can see at the end I get the result I want but in two requests. How to get it in one request?

total_device: 3, blacklisted: 1


Feel free to make any comment on all the SQL, I probably made few mistakes.

Thanks

Answer

You need a 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);