Postgresql join with Condition-CodePudding

I have three table as follows.

CREATE TABLE users (
    id uuid NOT NULL PRIMARY KEY,
    password character varying(128) NOT NULL,
    username character varying(15) NOT NULL,
    email character varying(100) NULL,
    gender character varying(1) NOT NULL  
  );


CREATE TABLE followers (
    id bigserial NOT NULL PRIMARY KEY,
    followed_at timestamp with time zone NOT NULL,
    follower_id uuid REFERENCES users(id),
    following_id uuid REFERENCES users(id)
  );


CREATE TABLE profile_picture (
    id uuid NOT NULL PRIMARY KEY ,
    profile_pic character varying(100) NOT NULL,
    owner_id uuid NOT NULL REFERENCES users(id),
    is_active boolean NOT NULL
  );

I want the query for selecting all the follower with the fields : id, username and active_profile_pic.

is_active will be true only for one profile pic of the user but he can upload as many profile photo as he want.

I have tried the without profile_pic, which is not the wanted result.

select users.id , username from users inner join followers on follower_id = users.id where followers.following_id = user_id;

I want the query for selecting all the followers with id, username and active_profile_pic who are following the user with given user_id.

User may have no profile pic and can have more than one so only the active_profile pic should be returned.

Query will have to contain the follower with no profile picture too.

Tried this, but it is not returning follower which have no profile picture. I want to return that too.

SELECT u.id, username, p.profile_pic  FROM followers INNER JOIN users AS u ON u.id = follower_id
LEFT OUTER JOIN profile_picture AS p ON u.id = p.owner_id
where followers.following_id = '' and p.is_active = true

In the above query user_id signifies variable , you can specify id of the user there.

Please visit DBFIDDLE

Please suggest me the right query.

CodePudding user response：

You have profile_picture.is_active test in WHERE condition, therefore your result will display only users with profile picture set. If you want all users, regardless of the profile picture, you should include profile_picture.is_active column in query results and delete the condition p.is_active = true from WHERE statement.

CodePudding user response：

With the help of @random_user,

This works for me.

select id , username, profile_pic from (SELECT u.id, username, is_active, profile_pic  FROM users as u Full Outer JOIN followers  ON u.id = follower_id
Full Outer JOIN profile_picture AS p ON u.id = p.owner_id
where followers.following_id = '2582f93d-68c3-48e2-98ba-a401402c7b62') as profile_table where (is_active = true) or (is_active is null)

May be someone can suggest, better answer, but this works for me.

Check it at DBFIDDLE