CODEWITHSUNDEEP
sqlpostgresqljoinselect
Vidlička LujVitóón
Vidlička LujVitóón

Reputation: 89

Join only when relation data exists

These are my tables.

CREATE TABLE IF NOT EXISTS categories (
  category_id serial primary key,
  category text not null,
  user_id int not null
);

CREATE TABLE IF NOT EXISTS activities (
  activity_id serial primary key,
  activity text not null,
  user_id int not null
);

CREATE TABLE IF NOT EXISTS categories_to_activities (
  category_id int not null REFERENCES categories (category_id) ON UPDATE CASCADE ON DELETE CASCADE,
  activity_id int not null REFERENCES activities (activity_id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT category_activity_pkey PRIMARY KEY (category_id, activity_id)
);

This is the query I'm using to get all activities with their categories.

SELECT a.*, ARRAY_AGG ( ROW_TO_JSON (c) ) categories
FROM activities a
JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
JOIN categories c ON ca.category_id = c.category_id
WHERE a.user_id = ${userId}
GROUP BY a.activity_id

The issue is that if an activity does not have a category assigned, it won't get returned.

I'm having trouble combining JOIN with CASE, which I suppose is what I need. Essentially I want to JOIN only when there is some record in categories_to_activities?

How do I do that?

Upvotes: 0

Views: 556

Answers (2)

Mureinik
Mureinik

Reputation: 311163

You could use a left join, and return nulls for activities without categories:

SELECT    a.*, 
          CASE WHEN ca.activity_id IS NOT NULL THEN ARRAY_AGG(ROW_TO_JSON(c)) 
                                               ELSE ARRAY[]::JSON[] 
          END as categories
FROM      activities a
LEFT JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
LEFT JOIN categories c ON ca.category_id = c.category_id
WHERE     a.user_id = ${userId}
GROUP BY  a.activity_id

Upvotes: 2

Vidlička LujVitóón
Vidlička LujVitóón

Reputation: 89

What I ended up with, thanks to @Murenik

SELECT    a.*,
          CASE WHEN ca.activity_id IS NOT NULL
            THEN ARRAY_AGG ( ROW_TO_JSON (c) )
            ELSE ARRAY[]::JSON[]
          END as categories
FROM      activities a
LEFT JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
LEFT JOIN categories c ON ca.category_id = c.category_id
WHERE     a.user_id = ${userId}
GROUP BY  a.activity_id, ca.activity_id

Upvotes: 0

Related Questions