CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8985

Count rows that are not in another table

I have the following tables that are for managing which profile has been used for what service.

profile table

CREATE TABLE public.profile
(
    id integer NOT NULL DEFAULT nextval('profile_id_seq'::regclass),
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT profile_id PRIMARY KEY (id)
)

service table

CREATE TABLE public.service
(
    id integer NOT NULL DEFAULT nextval('service_id_seq'::regclass),
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT service_id PRIMARY KEY (id)
)

used table

CREATE TABLE public.used
(
    id integer NOT NULL DEFAULT nextval('used_id_seq'::regclass),
    service_id bigint NOT NULL,
    profile_id bigint NOT NULL,
    insert_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT used_id PRIMARY KEY (id),
    CONSTRAINT used_profile_id_fkey FOREIGN KEY (profile_id)
        REFERENCES public.profile (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT used_service_id_fkey FOREIGN KEY (service_id)
        REFERENCES public.service (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

I'm trying to create a SQL query that would count the total profiles that profile_id is not in the used table for a specific service. Let's assume our service is called 'gym', how can I count number of profiles that are not in the used table with the service name of 'gym'?

Upvotes: 0

Views: 3082

Answers (1)

Amir Molaei
Amir Molaei

Reputation: 3820

First you need to join used table with service table to access the service text for each used record. Then you need to add the condition of service text being equal to gym and finally by using not in condition you can select the count of profiles that don't exist in the provided subquery.

select count(*) from public.profile
where id not in
(select u.profile_id from public.used u inner join public.service s
 on s.id = u.service_id
where s.text = 'gym')

Upvotes: 2

Related Questions