Обсуждение: Query Advice
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.
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.
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?
On 03/30/2017 12:13 PM, David G. Johnston wrote:
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.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.
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.
>
>
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.
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.