PostgreSQL multiple join issue

Question

Iam trying to create query with multiple JOINS, but Im receiving error message regarding syntax on second JOIN.

ERROR: syntax error at or near "INNER" LINE 19: INNER JOIN table2 AS jobinfo ON gaccess.... ^ SQL state: 42601 Character: 418

Can you help me what am I doing wrong?

SELECT
  access.id,
  access.user_id,
  access.device_id,
  access.origin,
  access.creation_date
FROM
  table1 access
  INNER JOIN (
    SELECT
      device_id,
      MAX (creation_date) AS creation_date
    FROM
      table1
    GROUP BY
      device_id
  ) gaccess ON access.device_id = gaccess.device_id
  AND access.creation_date = gaccess.creation_date;
  INNER JOIN 
    table2 AS jobinfo 
    ON gaccess.device_id = jobinfo.id

gotqn · Accepted Answer · 2023-02-08 08:11:42Z

2

You have a type - before the last INNER JOIN there is ; - you need to remove it.

SELECT access.id,
       access.user_id,
       access.device_id,
       access.origin,
       access.creation_date
FROM table1 access
INNER JOIN 
(
    SELECT device_id
          ,MAX(creation_date) AS creation_date
    FROM table1
    GROUP BY device_id
) gaccess 
    ON access.device_id = gaccess.device_id
    AND access.creation_date = gaccess.creation_date
INNER JOIN table2 AS jobinfo 
    ON gaccess.device_id = jobinfo.id;

answered Feb 8, 2023 at 8:11

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL multiple join issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related