Combine 4 tables in Postgres-CodePudding

I have four tables and I need to extract individual cost for every owner. The price of the procedure is in proceduredetails table and the name of the owner is from owners table. The two tables do not have the same column. I have to reference the pets table and the procedurehistory table to link them.

This is my current query

select owners.name ||' '|| owners.surname AS Owner, sum(proceduredetails.price) as price
from owners, proceduredetails
join pets p on owners.ownerid = p.ownerid
inner join procedurehistory p2 on p.petid = p2.petid
group by owners.ownerid;

I got an error

ERROR: invalid reference to FROM-clause entry for table "owners" Hint: There is an entry for table "owners", but it cannot be referenced from this part of the query.

CodePudding user response：

Apply inner join instead of cross join and proper relationship. As ownerid is used in group by clause so MAX() is used for owner name because ownerid is unique. Otherwise use ow.name ||' '|| ow.surname in group by clause instead of ownerid.

-- PostgreSQL
SELECT MAX(ow.name ||' '|| ow.surname) AS Owner
     , SUM(pd.price) as price
FROM owners ow
INNER JOIN pets p 
        ON ow.ownerid = p.ownerid
INNER JOIN procedurehistory p2 
        ON p.petid = p2.petid
INNER JOIN proceduredetails pd
        ON pd.proceduretype = p2.proceduretype      
GROUP BY ow.ownerid;