agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedQuery Advice
7+ messages / 6 participants
[nested] [flat]
* Query Advice
@ 2017-03-30 18:03 Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:13 ` Re: Query Advice David G. Johnston <david.g.johnston@gmail.com>
2017-03-30 18:16 ` Re: Query Advice Vincent Elschot <vinny@xs4all.nl>
2017-03-30 18:27 ` Re: Query Advice Jason Aleski <jason.aleski@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Gary Chambers @ 2017-03-30 18:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
@ 2017-03-30 18:13 ` David G. Johnston <david.g.johnston@gmail.com>
2017-03-30 18:17 ` Re: Query Advice Rob Sargent <robjsargent@gmail.com>
2017-03-30 19:19 ` Re: Query Advice Gary Chambers <gwc@gwcmail.com>
2017-03-30 19:22 ` Re: Query Advice Gary Chambers <gwchamb@gwcmail.com>
2 siblings, 3 replies; 7+ messages in thread
From: David G. Johnston @ 2017-03-30 18:13 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gwcmail.com>; +Cc: pgsql-sql
On Thu, Mar 30, 2017 at 11:03 AM, Gary Chambers <gwchamb@gwcmail.com> wrote:
> 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.
>
To comment as to "better" without any knowledge of the "application layer"
doesn't make sense.
I suggest you provide an example of what you'd like the output to look like
independent of any query concerns.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:13 ` Re: Query Advice David G. Johnston <david.g.johnston@gmail.com>
@ 2017-03-30 18:17 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2017-03-30 18:17 UTC (permalink / raw)
To: pgsql-sql
On 03/30/2017 12:13 PM, David G. Johnston wrote:
> On Thu, Mar 30, 2017 at 11:03 AM, Gary Chambers <gwchamb@gwcmail.com
> <mailto:gwchamb@gwcmail.com>>wrote:
>
> 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.
>
>
> To comment as to "better" without any knowledge of the "application
> layer" doesn't make sense.
>
> I suggest you provide an example of what you'd like the output to look
> like independent of any query concerns.
>
> David J.
>
If we assume the client layer is splitting by the pipe, why not just
send the set of addresses and the client simply iterates over that, no
hokey parsing involved.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:13 ` Re: Query Advice David G. Johnston <david.g.johnston@gmail.com>
@ 2017-03-30 19:19 ` Gary Chambers <gwc@gwcmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Gary Chambers @ 2017-03-30 19:19 UTC (permalink / raw)
To: pgsql-sql
Gentlemen,
Thank you for your replies.
I'm working on a Python Flask web application.
> To comment as to "better" without any knowledge of the "application layer"
> doesn't make sense. I suggest you provide an example of what you'd like
> the output to look like independent of any query concerns.
"Better" is to have the results returned without having the address columns
delimited by some character. The simple join that Jason suggested is
definitely sufficient, but it seems like returning the repeating name column
is unnecessary.
My question is really more theoretical than anything else. Vincent's
suggestion to return JSON is probably the closest to what I am trying to
accomplish. My ultimate goal is to have the equivalent of a Python list of
dictionaries as addresses, though the keys aren't a requirement as long as I
can define the order of the columns and avoid using the delimiter.
That being said, how would I accomplish this and return the data in the
usual SQL results format without using concat_ws()?
Thank you, again, for your advice.
--
G.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:13 ` Re: Query Advice David G. Johnston <david.g.johnston@gmail.com>
@ 2017-03-30 19:22 ` Gary Chambers <gwchamb@gwcmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Gary Chambers @ 2017-03-30 19:22 UTC (permalink / raw)
To: pgsql-sql
Gentlemen,
Thank you for your replies.
I'm working on a Python Flask web application.
> To comment as to "better" without any knowledge of the "application layer"
> doesn't make sense. I suggest you provide an example of what you'd like
> the output to look like independent of any query concerns.
"Better" is to have the results returned without having the address columns
delimited by some character. The simple join that Jason suggested is
definitely sufficient, but it seems like returning the repeating name column
is unnecessary.
My question is really more theoretical than anything else. Vincent's
suggestion to return JSON is probably the closest to what I am trying to
accomplish. My ultimate goal is to have the equivalent of a Python list of
dictionaries as addresses, though the keys aren't a requirement as long as I
can define the order of the columns and avoid using the delimiter.
That being said, how would I accomplish this and return the data in the
usual SQL results format without using concat_ws()?
Thank you, again, for your advice.
--
G.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
@ 2017-03-30 18:16 ` Vincent Elschot <vinny@xs4all.nl>
2 siblings, 0 replies; 7+ messages in thread
From: Vincent Elschot @ 2017-03-30 18:16 UTC (permalink / raw)
To: pgsql-sql
Op 30/03/2017 om 20:03 schreef Gary Chambers:
> 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.
>
>
"better" depends very much on your needs. I tend to return this kind of
data as a JSON string
because python (django) can be instructed to automatically translate
that into an array that I can loop through.
Do you have any particular reason for wanting to do this in one query,
given that you seem to want
a regular resultset for the addresses?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query Advice
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
@ 2017-03-30 18:27 ` Jason Aleski <jason.aleski@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Jason Aleski @ 2017-03-30 18:27 UTC (permalink / raw)
To: pgsql-sql
I agree, depends what the application is expecting, but if you only
wanted to return each field, would a regular JOIN work? This should
return each field in a separate column.
SELECT t1.name, t2.description, t2.addr, t2.city, t2.stprov, t2.zip
FROM company t1
JOIN postal_addresses AS t2 ON t1.company_id=t2.company_id
WHERE t1.company_id=1731;
-JA-
On 3/30/2017 1:03 PM, Gary Chambers wrote:
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-03-30 19:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-30 18:03 Query Advice Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:13 ` David G. Johnston <david.g.johnston@gmail.com>
2017-03-30 18:17 ` Rob Sargent <robjsargent@gmail.com>
2017-03-30 19:19 ` Gary Chambers <gwc@gwcmail.com>
2017-03-30 19:22 ` Gary Chambers <gwchamb@gwcmail.com>
2017-03-30 18:16 ` Vincent Elschot <vinny@xs4all.nl>
2017-03-30 18:27 ` Jason Aleski <jason.aleski@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox