Return rows if they exist in multiple JOIN table-CodePudding

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.*,
        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 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?

Tried also with:

AND EXISTS (SELECT 1
          FROM team_member_manager tmm 
          WHERE tmm.managing_team_member_id = team_member.id AND 
          tmm.managed_team_member_id = post.team_member_id
          )

Still no results.

CodePudding user response：

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