CODEWITHSUNDEEP
postgresql
vrybas
vrybas

Reputation: 1719

Has-Many-Through: How to select records with no relation OR by some condition in relation?

There are three tables: 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
);

So business has many categories through categorizations.

If I want to select businesses without categories, I would do something like this:

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;

The question is: How do I select businesses without categories AND businesses with category named "Media" in one query?

Upvotes: 1

Views: 58

Answers (3)

joop
joop

Reputation: 4523

The double-negation trick works for this kind of selections:

SELECT * FROM businesses b
WHERE NOT EXISTS (
    SELECT *
    FROM categorizations bc
    JOIN categories c ON bc.category_id = c.category_id
    WHERE bc.business_id = b.business_id
    AND c.name <> 'Media'
    );

Upvotes: 0

Mike K
Mike K

Reputation: 486

I would try:

SELECT b.* FROM businesses b
    LEFT JOIN categorizations cz ON b.business_id = cz.business_id
    LEFT JOIN categories cs ON cz.category_id = cs.category_id
    WHERE COALESCE(cs.name, 'Media') = 'Media';

... in the hope that businesses with no categorizations would get NULL entries on their joins.

Upvotes: 2

StuartLC
StuartLC

Reputation: 107357

You can use a union:

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';

Note that in the first instance (businesses with no categories at all) that you won't need to join as far as categories - you can detect the lack of category in the junction table. If it is possible for the same business to have the same category more than once, you'll need to introduce the second query with DISTINCT.

Upvotes: 2

Related Questions