clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926523 fiddles created (21781 in the last week).

create table hotels ( id int primary key, name text, vacancies int );
 hidden batch(es)


insert into hotels (id, name, vacancies) values (1, 'Marriott', 0), (2, 'Best Western', 10), (3, 'Sheraton', 3);
3 rows affected
 hidden batch(es)


create table amenities ( id int primary key, name text, key text );
 hidden batch(es)


insert into amenities (id, name, key) values (1, 'Cafe', 'cafe'), (2, 'Wheelchair Accessible', 'wheelchair_accessible'), (3, 'Wifi', 'wifi');
3 rows affected
 hidden batch(es)


create table hotels_amenities_lookup ( amenity_id int, hotel_id int, primary key (amenity_id, hotel_id) );
 hidden batch(es)


insert into hotels_amenities_lookup (amenity_id, hotel_id) values (1, 3), (2, 1), (2, 2), (2, 3), (3, 2), (3, 1);
6 rows affected
 hidden batch(es)


select h.name, h.vacancies from hotels h join ( select l.hotel_id, array_agg(a.key) amenities from hotels_amenities_lookup l join amenities a on a.id = l.amenity_id group by l.hotel_id ) a on a.hotel_id = h.id where a.amenities @> array['wifi', 'wheelchair_accessible'] ;
name vacancies
Best Western 10
Marriott 0
 hidden batch(es)