CODEWITHSUNDEEP
sqlpostgresql
user12214130
user12214130

Reputation: 158

Can set a limit for a joined table?

Is it possible to somehow set a limit 1 for a joined table masters but still not change the query much?

SELECT "rep_4hours"."hour_id", 
    "rep_4hours"."report_date", 
    "rep_4hours"."createdAt", 
    "rep_4hours"."updatedAt", 
    "rep_4hours"."teams_id", 
    "team"."team_id" AS "team.team_id", 
    "team"."full_name" AS "team.full_name", 
    "team"."is_active" AS "team.is_active", 
    "team"."createdAt" AS "team.createdAt", 
    "team"."updatedAt" AS "team.updatedAt", 
    "team->masters"."master_id" AS "team.masters.master_id", 
    "team->masters"."full_name" AS "team.masters.full_name", 
    "team->masters"."comment" AS "team.masters.comment", 
    "team->masters"."date_work" AS "team.masters.date_work", 
    "team->masters"."createdAt" AS "team.masters.createdAt", 
    "team->masters"."updatedAt" AS "team.masters.updatedAt", 
    "team->masters"."teams_id" AS "team.masters.teams_id" 
    FROM "rep_4hours" AS "rep_4hours" 
    INNER JOIN "teams" AS "team" ON "rep_4hours"."teams_id" = "team"."team_id" 
    INNER JOIN "masters" AS "team->masters" ON "team"."team_id" = "team->masters"."teams_id"
    WHERE ("report_date" >= "date_work") ORDER BY "team->masters"."date_work" DESC LIMIT 10 OFFSET 0;

Upvotes: 0

Views: 41

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13026

Here's your query. limit your masters using a subquery, and don't forget to include order by

SELECT "rep_4hours"."hour_id", 
    "rep_4hours"."report_date", 
    "rep_4hours"."createdAt", 
    "rep_4hours"."updatedAt", 
    "rep_4hours"."teams_id", 
    "team"."team_id" AS "team.team_id", 
    "team"."full_name" AS "team.full_name", 
    "team"."is_active" AS "team.is_active", 
    "team"."createdAt" AS "team.createdAt", 
    "team"."updatedAt" AS "team.updatedAt", 
    "team->masters"."master_id" AS "team.masters.master_id", 
    "team->masters"."full_name" AS "team.masters.full_name", 
    "team->masters"."comment" AS "team.masters.comment", 
    "team->masters"."date_work" AS "team.masters.date_work", 
    "team->masters"."createdAt" AS "team.masters.createdAt", 
    "team->masters"."updatedAt" AS "team.masters.updatedAt", 
    "team->masters"."teams_id" AS "team.masters.teams_id" 
FROM "rep_4hours" AS "rep_4hours" 
INNER JOIN "teams" AS "team" ON "rep_4hours"."teams_id" = "team"."team_id" 
INNER JOIN 
        (SELECT * FROM "masters" ORDER BY "date_work" DESC LIMIT 10) AS "team->masters" ON "team"."team_id" = "team->masters"."teams_id"
WHERE ("report_date" >= "date_work");

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

You can create derived table(sub-query) to limit masters data -

SELECT "rep_4hours"."hour_id", 
    "rep_4hours"."report_date", 
    "rep_4hours"."createdAt", 
    "rep_4hours"."updatedAt", 
    "rep_4hours"."teams_id", 
    "team"."team_id" AS "team.team_id", 
    "team"."full_name" AS "team.full_name", 
    "team"."is_active" AS "team.is_active", 
    "team"."createdAt" AS "team.createdAt", 
    "team"."updatedAt" AS "team.updatedAt", 
    "team->masters"."master_id" AS "team.masters.master_id", 
    "team->masters"."full_name" AS "team.masters.full_name", 
    "team->masters"."comment" AS "team.masters.comment", 
    "team->masters"."date_work" AS "team.masters.date_work", 
    "team->masters"."createdAt" AS "team.masters.createdAt", 
    "team->masters"."updatedAt" AS "team.masters.updatedAt", 
    "team->masters"."teams_id" AS "team.masters.teams_id" 
    FROM "rep_4hours" AS "rep_4hours" 
    INNER JOIN "teams" AS "team" ON "rep_4hours"."teams_id" = "team"."team_id" 
    INNER JOIN (SELECT * FROM "masters" LIMIT 1) AS "team->masters" ON "team"."team_id" = "team->masters"."teams_id"
    WHERE ("report_date" >= "date_work") ORDER BY "team->masters"."date_work" DESC LIMIT 10 OFFSET 0;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You may try doing a lateral join using LIMIT:

SELECT
    r.hour_id,
    r.report_date,
    r.createdAt,
    r.updatedAt,
    r.teams_id,
    t.team_id,
    t.full_name,
    t.is_active,
    t.createdAt,
    t.updatedAt,
    m.master_id,
    m.full_name,
    m.comment,
    m.date_work,
    m.createdAt,
    m.updatedAt,
    m.teams_id,
FROM rep_4hours r
INNER JOIN teams t
    ON r.teams_id = t.team_id
INNER JOIN LATERAL
(
    SELECT m1.*
    FROM masters m1
    WHERE m1.teams_id = t.team_id
    ORDER BY <some_col>   -- LIMIT only makes sense with an ORDER BY clause
    LIMIT 1
) m
    ON TRUE
WHERE
    report_date >= date_work
ORDER BY
   m.date_work DESC
LIMIT 10 OFFSET 0;

Note that the lateral LIMIT subquery really needs to have an ORDER BY clause in order for limit to make any sense. Otherwise, it is not clear which first record you want.

Upvotes: 0

Related Questions