I have 3 tables:

1. Scorecard
2. Group
3. Scorecard_Group (Joint table)

I am trying to write a query that fulfills the following purpose:

1. Get the list of distinct scorecard ids WHERE Scorecard_Group.groupId IN (Pass array of groupIds)
2. Get all Scorecard."name" where Scorecard."id" IN (Array of scorecardIds that we just queries)

I am able to achieve this using 2 queries but I would like for an optimal solution. Here is my 2 queries solution:

SELECT array_agg(DISTINCT sg."scorecardId") as "ids"
FROM scorecard_group sg
WHERE sg."groupId" IN (${groupIds.concat()})


SELECT sc."id", sc."name"
FROM "scorecard" sc
WHERE sc."id" IN (${scIds[0].ids.concat()})

I tried to achieve the same result in 1 query but I am getting:

column "scIds" does not exist

SELECT sc."name", sc."id",
       ( 
   SELECT DISTINCT sg."scorecardId"
   FROM scorecard_group sg
   WHERE sg."groupId" IN (${groupIds.concat()})
       ) as "scIds"
FROM "scorecard" sc
WHERE sc."id" IN ("scIds")

CodePudding user response：

Assuming a proper many-to-many implementation like:

• How to implement a many-to-many relationship in PostgreSQL?

Seems like you twisted / over-complicated your objective as well as your query.

SELECT DISTINCT s.id, s.name
FROM   scorecard_group sg
JOIN   scorecard       s  ON s.id = sg.scorecard_id
WHERE  sg.group_id = ANY ('{1,2,3}')  -- your array of group_id's here!
ORDER  BY 1, 2;  -- optional order

This retrieves the full, distinct set of scorecards (id, name) that are member in the given groups (array of group_id's).

Or, the same a bit faster for many duplicate memberships:

SELECT s.scorecard_id, s.name
FROM   scoeguard s
JOIN  (
   SELECT DISTINCT sg.scorecard_id
   FROM   scorecard_group sg
   WHERE  sg.group_id = ANY ('{1,2,3}')  -- your array of group_id's here!
   ) sg ON sg.scorecard_id = s.id
ORDER  BY 1, 2;  -- optional order

Using legal, lower-case, unquoted identifiers like you should, too. See:

• Are PostgreSQL column names case-sensitive?

To use IN, you'd have to provide a set or list.
Use = ANY for an array. See:

• IN vs ANY operator in PostgreSQL