Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table ads (
  id serial primary key,
  description text,
  status text
);

create table ads_features (
  id serial primary key,
  ad integer not null references ads,
  feature text
);

CREATE TABLE



CREATE TABLE


insert into ads(description, status) values
  ('one feature', 'active'),
  ('two features', 'active'), 
  ('two features, inactive', 'inactive'), 
  ('no features', 'active'), 
  ('three features', 'active');

INSERT 0 5


insert into ads_features(ad, feature) values
  (1, 'feature1'),
  (2, 'feature2'), (2, 'feature1'),
  (3, 'feature2'), (3, 'feature1'),
  (5, 'feature1'), (5, 'feature2'), (5, 'feature3');

INSERT 0 8


select
  ads.*,
  array_agg(feature) as features
from ads
join ads_features
  on ads.id = ads_features.ad
where ads.status = 'active'
group by ads.id


id
description
status
features


1
one feature
active
{feature1}

2
two features
active
{feature2,feature1}

5
three features
active
{feature1,feature2,feature3}



SELECT 3


select
  ads.*,
  array_agg(feature) as features
from ads
join ads_features
  on ads.id = ads_features.ad
where ads.status = 'active'
group by ads.id
having array_agg(feature) @> array['feature2', 'feature1']


id
description
status
features


2
two features
active
{feature2,feature1}

5
three features
active
{feature1,feature2,feature3}



SELECT 2

id	description	status	features
1	one feature	active	{feature1}
2	two features	active	{feature2,feature1}
5	three features	active	{feature1,feature2,feature3}