Oracle --> Postgres query

Question

I'm an Oracle guy trying to convert a query like this to Postgres. Not really understanding all the syntax, etc. Was hoping someone can help?

Oracle 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(+);

Much appreciated

D-Shih · Accepted Answer · 2018-09-17 17:07:31Z

2

You can try to use OUTER JOIN

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

You seem like didn't need to use subquery, because you didn't do anything in the subquery only select original columns. you can query the table directly.

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

edited Sep 17, 2018 at 17:07

answered Sep 17, 2018 at 17:06

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

1 Comment

wildplasser Over a year ago

IIRC the (+) is a LEFT JOIN

Collectives™ on Stack Overflow

Oracle --> Postgres query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related