PostgreSQL: Create Boolean field based on multiple rows from join

Question

I am gathering many fields from one database over many tables (6). One column is causing duplication for some of the data. I want to create a boolean for that field if one or more of my parameters is true and group by the rest of the fields.

There is a one to many relationship between the geometry table and the address table (2 sides to every road). So, the problem is that there will be one value for each side of the road, therefore multiple returns. I've played around with bool_or, case, coalesce, exists, array_agg but can't figure it out. Any ideas?

select g.country, g.id, n.name, gsc.geometry, gst.class, gst.length, gst.road_type,     gst.one_way, gst.bridge, ga.add_type
from geo g
left outer join name n on (g.id = n.id)
left outer join geometry_address ga on (g.id = ga.id)
join geometry_s gst on (g.is = gst.is)
join geometry_s gs on (g.id = gs.id)
join geometry_sc gsc on (gs.gsi = gsc.gsi)
where (g.type = 'road') and (g.country = 'USA')

Thanks for your help.

You want only one side of the road? BTW: you are only selecting `ga.add_type` from the geometry_address table. The dependent data (coordinates?) is supposed to come from the geo-table? (I'd expect the ordering of geo and geometry_address+name name inverted wrt the `LEFT JOIN` . (cannot tell without the data model, though) — wildplasser, Aug 15 '13 at 22:56

score 0 · Answer 1 · answered Nov 10 '13 at 14:24

First you need to provide what you are doing and which fields are the problem. However basically, the following is what I would use.

      SELECT t1.fld1, t1.fld2, bool_or(t2.fld1)
        FROM mytable t1
   LEFT JOIN my_other_table t2 ON (t1.pkey1 = t2.fkey_mytable)
    GROUP BY t1.fld1, t1.fld2;

A few recommendations in building large queries in unfamiliar territory because that may be where the problem is:

Start with a minimal join in your problem area. Play with it until it works. Then add the rest.
Keep careful track of errors and what you have tried.

PostgreSQL: Create Boolean field based on multiple rows from join

1 Answers1