postgres duplicate results in multiple inner join

Question

postgres duplicate results in multiple inner join

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a db in postgres with the following structure.

tbl_predio
===========
predio_id
predio_nombre

tbl_propietario
==============
propietario_id
propietario_nombre
propietario_idpredio

tbl_linderos
===========
lindero_id
lindero_nombre
lindero_idpredio

The problem I have is that if I have more than one boundary for a property I doubled the owners in a query with a join.

That is, assuming that for the property of id 1 I have a single owner named John, and I have two boundaries north boundary1 and boundary2 when assembling a query returns me to owners John, John.

I am asking the question like this:

SELECT 
  p.predio_nombre AS predio,
  string_agg(pr.propietario_nombre,',') AS propietarios,
  string_agg(l.lindero_nombre,',') AS linderos

FROM tbl_predio p
  LEFT JOIN tbl_propietario pr ON pr.propietario_idpredio = p.predio_id
  LEFT JOIN tbl_linderos l ON l.lindero_idpredio = p.predio_id

WHERE
  p.predio_id = '1'

GROUP BY p.predio_id

The result I'm getting is

predio | propietarios | linderos
================================
predio1|john,john     | lindero1,lindero2

the result I want is

predio | propietarios | linderos
================================
predio1|john     | lindero1,lindero2

In short, I'm getting as many owners as there are boundaries, if I have 3 boundaries, I'll put 3 times each existing owner in the DB.

join postgresql

asked by Faryd Montenegro 24.08.2016 в 16:08

source

2 answers

0

this could help you

SELECT 
  p.predio_nombre AS predio,
  (select string_agg(pr1.propietario_nombre,','  ORDER BY pr1.propietario_id DESC) from tbl_propietario pr1 where pr1.propietario_idpredio=p.predio_id) AS propietarios,
  string_agg(l.lindero_nombre,',') AS linderos

FROM tbl_predio p
  LEFT JOIN tbl_linderos l ON l.lindero_idpredio = p.predio_id

WHERE
  p.predio_id = '1'

GROUP BY p.predio_id

answered by 08.01.2017 в 00:40

How to make records in the database with PHP? script to support mysql databases in php, but I need to migrate to mysqli

score 1 · Accepted Answer

You could use the keyword distinct , such that only the different values return in the aggregation:

SELECT 
  [...]
  string_agg(distinct(pr.propietario_nombre),',') AS propietarios,
  string_agg(distinct(l.lindero_nombre),',') AS linderos
[...]