Date: Thu, 30 Mar 2017 14:03:22 -0400 (EDT)
From: Gary Chambers <gwchamb@gwcmail.com>
To: PostgreSQL SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Query Advice
Message-ID: <alpine.DEB.2.20.1703301350250.17355@websrv.gogca.com>
User-Agent: Alpine 2.20 (DEB 67 2015-01-07)
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

All,

Given the following tables:

company
-------
company_id
name

postal_addresses
----------------
postal_address_id
company_id
description
addr
city
stprov
zip


I've been handling joins as such:

select c.company_id,
        array(select concat_ws('|', pa.description, pa.addr, pa.city,
                               pa.stprov, pa.zip)
        ) addrs
from companies c inner join postal_addresses pa using (company_id)
where company_id = 1731;

Is there a better way to get the company information along with all of the
addresses in a single query?  This works, but it requires the additional
step of splitting the addresses by the the delimiter at the application
layer.

Thanks for any advice you have.

--
G.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql