postgresql - 有多次通过 : How to select records with no relation OR by some condition in relation?

Question

共有三个表:businesses、categories、categorizations、

CREATE TABLE businesses (
  id SERIAL PRIMARY KEY,
  name varchar(40)
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name varchar(40)
);

CREATE TABLE categorizations (
  business_id integer,
  category_id integer
);

所以 business 通过 categorizations 有很多 categories。

如果我想选择没有类别的商家，我会做一些事情像这样:

SELECT businesses.* FROM businesses
LEFT OUTER JOIN categorizations
ON categorizations.business_id = businesses.id
LEFT OUTER JOIN categories
ON categories.id = categorizations.category_id
GROUP BY businesses.id
HAVING count(categories.id) = 0;

问题是:我如何选择没有类别的商家并且在一个查询中使用名为“媒体”的类别的企业？

Answer 1

你可以使用联合:

SELECT businesses.* 
FROM businesses
LEFT OUTER JOIN categorizations
ON categorizations.business_id = businesses.id
GROUP BY businesses.id
HAVING count(categorizations.business_id) = 0

UNION 

SELECT businesses.* 
FROM businesses
INNER JOIN categorizations
ON categorizations.business_id = businesses.id
INNER JOIN categories
ON categories.id = categorizations.category_id
WHERE categories.name = 'Media';

请注意，在第一种情况下(根本没有类别的企业)您不需要连接到类别 - 您可以检测到连接表中缺少类别。如果同一商家可能多次使用同一类别，则需要使用 DISTINCT 引入第二个查询。