Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE partner (
  partner_id serial PRIMARY KEY
, partner    text
);
INSERT INTO partner (partner) VALUES 
  ('partner1')
, ('partner2')
, ('partner3')
, ('partner4')
;

CREATE TABLE category (
  category_id serial PRIMARY KEY
, category    text
);
INSERT INTO category (category) VALUES 
  ('categ1')
, ('categ2')
, ('business')
, ('retail')
;

CREATE TABLE partner_category (
  partner_id  int REFERENCES partner(partner_id)
, category_id int REFERENCES category(category_id)
, CONSTRAINT cat_pk PRIMARY KEY (partner_id, category_id)
);
INSERT INTO partner_category (partner_id, category_id) VALUES 
  (1,1), (1,2), (1,3)
, (2,4)
, (3,3), (3,4)
, (4,1), (4,2);

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 8


SELECT p.*
FROM   partner p
WHERE  EXISTS (SELECT FROM partner_category pc WHERE pc.partner_id = p.partner_id AND pc.category_id = 3)
OR     EXISTS (SELECT FROM partner_category pc WHERE pc.partner_id = p.partner_id AND pc.category_id = 4)
ORDER  BY p.partner_id;


partner_id
partner


1
partner1

2
partner2

3
partner3



SELECT 3


SELECT p.*
FROM        (SELECT partner_id FROM partner_category WHERE category_id = 3) pc1
FULL   JOIN (SELECT partner_id FROM partner_category WHERE category_id = 4) pc2 USING (partner_id)
JOIN   partner p USING (partner_id)
ORDER  BY p.partner_id;


partner_id
partner


1
partner1

2
partner2

3
partner3



SELECT 3