Trouble with adding additional column with JOIN-CodePudding

Have two tables:

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.

CodePudding user response：

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

CodePudding user response：

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.