Question 90367 in PostgreSQL Question posted in
The official documentation can be found here.

Trouble with adding additional column with JOIN – Postgresql

Have two tables:

enter image description here

Task:

Make a table with fields:

  • name of acquiring_company;
  • name of acquired_company;
  • price_amount;
  • funding_total;

Ignore those transactions in which the purchase amount (price_amount) and amount of investment (funding_total) is zero

SELECT
a.acquiring_company_id,
a.acquired_company_id,
a.price_amount,
c.funding_total
FROM acquisition a
LEFT JOIN company c
    ON a.acquired_company_id = c.id
WHERE price_amount <> 0 AND funding_total <> 0

Maybe a dumb question, but how can I add names to acquiring_company and acquired_company (company.name)? I don’t understand it because there are two id fields. I can’t use JOIN on both of them.

Tags:

2

Answers


  1. 0

    Join the same table twice with different alias names

    SELECT a.acquired_company_id, c1.name as acquired_company_name,
       a.acquiring_company_id, c2.name as acquiring_company_name,           
       a.price_amount,
       c1.funding_total
FROM acquisition a
LEFT JOIN company c1 ON a.acquired_company_id = c1.id
LEFT JOIN company c2 ON a.acquiring_company_id = c2.id
WHERE a.price_amount <> 0 
  AND c1.funding_total <> 0
    Login or Signup to reply.
  2. 0

    I can’t use JOIN on both of them.

    Yes, you can. And should.

    SELECT
    a.acquiring_company_id,
    a.acquired_company_id,
    a.price_amount,
    c1.funding_total,
    c1.name as acquired_company_name,
    c2.name as acquiring_company_name
FROM acquisition a
LEFT JOIN company c1
    ON a.acquired_company_id = c1.id
LEFT JOIN company c2
    ON a.acquiring_company_id = c2.id
WHERE price_amount <> 0 AND c1.funding_total <> 0

    Test the query in Db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search