Setup
这是您应该提供的测试用例:
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);
Solution
One way:
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;
Another:
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;
fiddle https://dbfiddle.uk/q1MO2O23
Old sqlfiddle http://sqlfiddle.com/#!17/2870a/1
第二个假设是唯一的(partner_id, category_id)
in partner_category
.