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](https://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-syntax-vs-ansi-syntax) — Lukasz Szozda, Sep 17 '18 at 20:11
https://stackoverflow.com/questions/52372617/oracle-postgres-query — wildplasser, Sep 17 '18 at 21:35

score 2 · Answer 1 · 2018-09-17T20:15:48.627

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

score 2 · Answer 2 · answered Sep 17 '18 at 20:12

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.

Works great! Thank you. – Landon Statis Sep 18 '18 at 14:55 — Landon Statis, Sep 18 '18 at 14:55

Oracle query --> Postgres same query

2 Answers2