Oracle query --> Postgres same query

Question

earlier I had asked for some help on an Oracle query I was trying to make into a Postgres query:

SELECT c.code, c.recommendation, s.suggested, s.sugg_by, a.approved, a.app_by
FROM (SELECT code, recommendation FROM recommendations) c,
     (SELECT code, suggested, sugg_by FROM suggestions) s,
     (SELECT code, approved, app_by FROM suggestions) a
WHERE c.code = s.code(+)
  AND c.code = a.code(+);

Someone recommended this, and it works fine:

SELECT
    c.code,
    c.recommendation, 
    s.suggested,
    s.sugg_by, 
    a.approved,
    a.app_by
FROM recommendations c 
LEFT JOIN suggestions s on c.code = s.code
LEFT JOIN suggestions a ON c.code = a.code;

I forgot to mention one thing: I need to add in a join to a USERS table to get the users name.

sugg_by = u.user_id
app_by = u.user_id

So, something like this:

SELECT c.code, c.recommendation, s.suggested, s.sugg_by, s.full_name, a.approved, a.app_by, a.full_name
FROM (SELECT code, recommendation FROM recommendations) c,
     (SELECT code, suggested, sugg_by, full_name FROM suggestions s, users u WHERE s.sugg_by = u.user_id) s,
     (SELECT code, approved, app_by, full_name FROM approvals a, users u WHERE a.app_by = u.user_id) a
WHERE c.code = s.code(+)
  AND c.code = a.code(+);

Basically I need the names also from the USERS table. Easy enough in Oracle, just trying to get this together for Postgres.

Easy enough in Oracle I suggest to swithc to use explicit JOINs. It will pay off in the long run. Oracle Joins - Comparison between conventional syntax VS ANSI Syntax — Lukasz Szozda
– Lukasz Szozda, Commented Sep 17, 2018 at 20:11

Allan · Accepted Answer · 2018-09-17 20:12:37Z

2

You don't need all of the sub-queries:

SELECT c.code,
       c.recommendation,
       s.suggested,
       s.sugg_by,
       su.full_name,
       a.approved,
       a.app_by,
       au.full_name
FROM   recommendations c
       LEFT JOIN suggestions s ON c.code = s.code
       LEFT JOIN users su ON s.sugg_by = su.user_id
       LEFT JOIN approvals a ON c.code = a.code
       LEFT JOIN users au ON a.sugg_by = au.user_id;

Incidentally, this should work in both Oracle and Postgres.

answered Sep 17, 2018 at 20:12

Allan

17.5k5 gold badges54 silver badges73 bronze badges

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

1 Comment

Landon Statis Over a year ago

Works great! Thank you.

score 2 · Accepted Answer · 2018-09-17 20:15:48Z

You can join to a query the same way you join to a "plain" table reference:

SELECT c.code, 
       c.recommendation, 
       s.suggested, 
       s.sugg_by, 
       s.full_name, 
       a.approved, 
       a.app_by, 
       a.full_name
FROM recommendations c
  LEFT JOIN (
    SELECT code, suggested, sugg_by, full_name 
    FROM suggestions s 
      JOIN users u ON s.sugg_by = u.user_id
  ) s ON c.code = s.code
  LEFT JOIN (
    SELECT code, approved, app_by, full_name 
    FROM approvals a
      JOIN users u ON a.app_by = u.user_id
  ) a on c.code = a.code

But as Allan pointed out: you don' really need those derived table ("subqueries"). It's much easier doing things like that using explicit JOIN operators (or LEFT JOIN) rather than Oracle's archaic and proprietary (+) operator (even Oracle recommends to stop using it).

Collectives™ on Stack Overflow

Oracle query --> Postgres same query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related