CODEWITHSUNDEEP
sqlpostgresqljoin
jabepa
jabepa

Reputation: 61

Return rows if they exist in multiple JOIN table

UPDATE: Can someone help with this?

I edited my sql query to working example but still getting all rows from post_comment_response_approval. We should not return any rows for those who do not exist in that team_member_manager table (even if they have a record in post_comment_response_approval) --> see table examples

I am writing an sql query within Postgres 12.

The query should return rows for all members in that post_comment_response table, and it should not return any rows for those who do not exist in that team_member_manager table (even if they have a record in post_comment_response_approval).

    SELECT
        post_comment_response.*,
        SELECT JSON_AGG(approvals_inner) FROM
            (
                SELECT
                    pcra.team_member_id,
                    pcra.note
                FROM post_comment_response_approval pcra
            ) AS approvals_inner
        ) AS approvals,
        count(*) OVER() AS total_count
    FROM post_comment_response
    LEFT JOIN post_comment ON post_comment.id = post_comment_response.post_comment_id
    JOIN post ON post.id = post_comment.post_id
    JOIN team_member ON team_member.id = post.team_member_id
    JOIN team_member_manager tmm ON (tmm.managing_team_member_id = team_member.id AND tmm.managed_team_member_id = post.team_member_id)
    WHERE team_member.team_id = 91

I want to geather results using mutiple joins but on adding JOIN team_member_manager tmm ON (tmm.managing_team_member_id = team_member.id AND tmm.managed_team_member_id = post.team_member_id) I do not get any results..

managing_team_member_id is the one with defined value (91)

managed_team_member_id value I want to get by JOINing post_comment → post and getting post.team_member_id.

I was careful with syntax and I do not know why do I not get any results..

The tables are below:

post_comment_response table:

id post_comment_id comment
1 1173 Hello World

post_comment_response_approval table:

id post_comment_response_id team_member_id approved note
54 1 60735 true This one should be listed
70 1 666 true This should not

post_comment table:

id post_id
1173 652

post table:

id message_id team_member_id
652 110 60735

team_member_manager table:

id managing_team_member_id managed_team_member_id
55 68893 60735

team_member table:

id team_id member_id
68893 91 1

I can not get my head around of what I am doing wrong? Can someone please help?

Upvotes: 0

Views: 80

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

From your explanation I think your mistake is that you check the posting member's team ID, when you want to the check the posting member's manager's team ID. This means you must join again to the team_member table to see the manager's data.

SELECT
  pcr.*,
  COUNT(*) OVER() AS total_count
FROM post_comment_response pcr
JOIN post_comment pc ON pc.id = pcr.post_comment_id
JOIN post p ON p.id = pc.post_id
JOIN team_member tm ON tm.id = p.team_member_id
JOIN team_member_manager tmm ON tmm.managed_team_member_id = tm.id
JOIN team_member mgr ON mgr.id = tmm.managing_team_member_id
WHERE mgr.team_id = 91;

I think this query makes the relations very clear. A post is posted by a member, that member has a manager, and the manager themselves is also a member in the member table.

As you don't need any data from the posting member, though, you can remove this join from the query:

SELECT
  pcr.*,
  COUNT(*) OVER() AS total_count
FROM post_comment_response pcr
JOIN post_comment pc ON pc.id = pcr.post_comment_id
JOIN post p ON p.id = pc.post_id
JOIN team_member_manager tmm ON tmm.managed_team_member_id = p.team_member_id
JOIN team_member mgr ON mgr.id = tmm.managing_team_member_id
WHERE mgr.team_id = 91;

The same with an IN clause:

SELECT
  pcr.*,
  COUNT(*) OVER() AS total_count
FROM post_comment_response pcr
WHERE post_comment_id IN
(
  SELECT pc.id
  FROM post_comment pc
  JOIN post p ON p.id = pc.post_id
  JOIN team_member_manager tmm ON tmm.managed_team_member_id = p.team_member_id
  JOIN team_member mgr ON mgr.id = tmm.managing_team_member_id
  WHERE mgr.team_id = 91
);

Upvotes: 1

Related Questions