Search code examples
postgresqljoinduplicatesmany-to-many

How to find in a many to many relation all the identical values in a column and join the table with other three tables?

I have a many to many relation with three columns, (owner_id,property_id,ownership_perc) and for this table applies (many owners have many properties).

So I would like to find all the owner_id who has many properties (property_id) and connect them with other three tables (Table 1,3,4) in order to get further information for the requested result. All the tables that I'm using are

Table 1: owner (id_owner,name)
Table 2: owner_property (owner_id,property_id,ownership_perc)
Table 3: property(id_property,building_id)
Table 4: building(id_building,address,region)

So, when I'm trying it like this, the query runs but it returns empty.

SELECT address,region,name
FROM owner_property
JOIN property ON owner_property.property_id = property.id_property
JOIN owner ON owner.id_owner = owner_property.owner_id
JOIN building ON property.building_id=building.id_building
GROUP BY owner_id,address,region,name
HAVING count(owner_id) > 1
ORDER BY owner_id;

Only when I'm trying the code below, it returns the owner_id who has many properties (see image below) but without joining it with the other three tables:

enter image description here

SELECT a.*
FROM owner_property a
JOIN (SELECT owner_id, COUNT(owner_id)
FROM owner_property
GROUP BY owner_id
HAVING COUNT(owner_id)>1) b
ON a.owner_id = b.owner_id
ORDER BY a.owner_id,property_id ASC;

So, is there any suggestion on what I'm doing wrong when I'm joining the tables? Thank you!

Solution

  • This query:

    SELECT owner_id
FROM owner_property
GROUP BY owner_id
HAVING COUNT(property_id) > 1

    returns all the owner_ids with more than 1 property_ids.
    If there is a case of duplicates in the combination of owner_id and property_id then instead of COUNT(property_id) use COUNT(DISTINCT property_id) in the HAVING clause.
    So join it to the other tables:

    SELECT b.address, b.region, o.name
FROM (
  SELECT owner_id
  FROM owner_property
  GROUP BY owner_id
  HAVING COUNT(property_id) > 1
) t
INNER JOIN owner_property op ON op.owner_id = t.owner_id 
INNER JOIN property p ON op.property_id = p.id_property
INNER JOIN owner o ON o.id_owner = op.owner_id
INNER JOIN building b ON p.building_id = b.id_building
ORDER BY op.owner_id, op.property_id ASC;

    Always qualify the column names with the table name/alias.