[SQL] SQL dealing with subquery
Hi all,
I'm having a conceptual problem with a subquery here - any help would be
appreciated.
I have a table treaty_rates with columns payor, payee, rate where payor and
payee are countries.
Sample set:
'US','UK',5
'US','Ireland',5
'US','Netherlands',5
'US','China',10
'Canada','US',0
'Canada','Ireland',0
'Canada','Netherlands',5
'Canada','UK,5
'Belgium','Netherlands',0
There is no certainty that the same payees exist for each payor (i.e. not
all countries have treaties with other all other countries)
I want to select all rows where the payee is in some array (e.g, in
('Netherlands','Ireland')) and the rate is not the same
In other words, in the sample above, I only want to return:
'Canada','Ireland',0
'Canada','Netherlands',5
The initial query to limit the rows to the specified payees is obvious, but
I can't get my mind around how to compare each set of rows where the payor
is the same country. Running select payor, payee, rate where payee in
('Netherlands','Ireland') will return 132 rows (66 pairs).
Any suggestions on how to compare the pairs would be appreciated.
Bryan
[SQL] SQL dealing with subquery comparison
Hi all,
I'm having a conceptual problem with a subquery here - any help would be
appreciated.
I have a table treaty_rates with columns payor, payee, rate where payor and
payee are countries.
Sample set:
'US','UK',5
'US','Ireland',5
'US','Netherlands',5
'US','China',10
'Canada','US',0
'Canada','Ireland',0
'Canada','Netherlands',5
'Canada','UK,5
'Belgium','Netherlands',0
There is no certainty that the same payees exist for each payor (i.e. not
all countries have treaties with other all other countries)
I want to select all rows where the payee is in some array (e.g, in
('Netherlands','Ireland')) and the rate is not the same
In other words, in the sample above, I only want to return:
'Canada','Ireland',0
'Canada','Netherlands',5
The initial query to limit the rows to the specified payees is obvious, but
I can't get my mind around how to compare each set of rows where the payor
is the same country. Running select payor, payee, rate where payee in
('Netherlands','Ireland') will return 132 rows (66 pairs).
Any suggestions on how to compare the pairs would be appreciated. (Obviously
I could also run into someone asking me for more than 2 payees and asking
for the combination payor/payee with the lowest rate for each individual
payor).
Thanks,
Bryan
Re: [SQL] SQL dealing with subquery
Thanks. It throws off a few extra countries where there is only one treaty, but
those are
few enough that I can handle them manually.
I thought the solution was also going to give me insight into how to select
just the lowest
rate from each couple, (i.e. for each payor, who is the lowest rate payee) but
it looks like
I'll have find some time to think about that later (I've also got to think
about what to do in tie situations.).
Again, thanks.
Bryan
On Tuesday 15 January 2008 12:40:13 pm Rodrigo E. De León Plicet wrote:
> On Jan 15, 2008 1:04 PM, Bryan Emrys <[EMAIL PROTECTED]> wrote:
> > In other words, in the sample above, I only want to return:
> > 'Canada','Ireland',0
> > 'Canada','Netherlands',5
>
> Try (untested):
>
> SELECT t2.*
> FROM (SELECT payor
> FROM treaty_rates
> WHEREpayee IN ('Netherlands', 'Ireland')
> GROUP BY payor
> HAVING MIN (rate) != MAX (rate)) t1
>JOIN
>treaty_rates t2 ON t1.payor = t2.payor
> WHERE t2.payee IN ('Netherlands', 'Ireland');
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SQL dealing with subquery
Following up my treaty rate thoughts, if I'm trying to get the lowest treaty
payee (and rate) from a specific list of payees for every possible payor
country, the following seems to work, but is it right? I'm specifically
wondering about the group by clauses. (Or if there is a better way.) [table
treaties has columns payor,payee,rate and in this sample, I'm just trying to
find which country payee between Ireland and the Netherlands has the lowest
rate from each individual payor country.]
select a.payor,a.payee,a.rate
from treaties a,
(select payor,min(rentr) from treaties
where payee in ('Ireland','Netherlands') group by payor
) b
where a.payor=b.payor
and a.rate = b.min
and payee in ('Ireland','Netherlands')
group by a.payor, a.payee, a.rate
[SQL] Data Comparison Single Table Question
I can handle this outside sql, but it seems like I should be able to do this in sql as well. 1 table: countries. 3 columns: id, name, price What I'm trying to get is a result of the price differences between every country. So if the data looks like (ignoring the id field) Taiwain 30 UK 50 US 40 I'm trying to build a matrix that looks like: Taiwan UK US Taiwan 0 -20 -10 UK 20 0 10 US 10 -10 0 Any pointers would be appreciated. Bryan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Conceptual Design Question
Hello Everyone, In a text-heavy database, I'm trying to make an initial design decision in the following context. There is a lot of long text that I could break down into three different categories: a. Laws i. Only 1 country per law, many laws ii. There are multiple types of laws (statutes, regulations, court cases, proposed laws, etc) iii. Each law will have only one type iv. Each law may refer to many other laws b. Treaties i. 2 countries per treaty ii. At any one time, there will only be one treaty in force between any two countries iii. There may be proposed new treaties which will supercede old treaties when finally ratified c. Commentary i. Any commentary could refer to one or more laws or treaties ii. Any commentary may have one or more authors iii. Any commentary may refer to one or more countries The conceptual question is what are the trade-offs between having one textual table compared with multiple text tables? Any help on pointing out practical considerations would be appreciated. Thanks. Bryan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Apologies to the list. Please ignore accidental thread intrusion
Sorry about that. Bryan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] One Text Table or Multiple Text Tables - Design Tradeoffs?
(Trying to start a new thread instead of my accidental intrusion into another thread) Hello Everyone, In a text-heavy database, I'm trying to make an initial design decision in the following context. There is a lot of long text that I could break down into three different categories: a. Laws i. Only 1 country per law, many laws ii. There are multiple types of laws (statutes, regulations, court cases, proposed laws, etc) iii. Each law will have only one type iv. Each law may refer to many other laws b. Treaties i. 2 countries per treaty ii. At any one time, there will only be one treaty in force between any two countries iii. There may be proposed new treaties which will supercede old treaties when finally ratified c. Commentary i. Any commentary could refer to one or more laws or treaties ii. Any commentary may have one or more authors iii. Any commentary may refer to one or more countries The conceptual question is what are the trade-offs between having one textual table compared with multiple text tables? Any help on pointing out practical considerations would be appreciated. Thanks. Bryan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
