Thread: SQL dealing with subquery

Hi all,<br />I'm having a conceptual problem with a subquery here - any help would be appreciated.<br /><br />I have a
tabletreaty_rates with columns payor, payee, rate where payor and payee are countries.<br />Sample set: <br />
'US','UK',5<br/>'US','Ireland',5<br />'US','Netherlands',5<br />'US','China',10<br />'Canada','US',0<br
/>'Canada','Ireland',0<br/>'Canada','Netherlands',5 <br />'Canada','UK,5<br />'Belgium','Netherlands',0<br /><br
/>Thereis no certainty  that the same payees exist for each payor (i.e. not all countries have treaties with other all
othercountries)<br /><br />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<br /><br />In other words, in the sample above, I only want to
return:<br/>'Canada','Ireland',0 <br />'Canada','Netherlands',5<br /><br />The initial query to limit the rows to the
specifiedpayees 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). <br
/><br/>Any suggestions on how to compare the pairs would be appreciated. <br /><br />Bryan<br /> 

On Jan 15, 2008 1:04 PM, Bryan Emrys <bryan.emrys@gmail.com> 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       WHERE    payee 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');

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 <bryan.emrys@gmail.com> 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
>         WHERE    payee 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');
>

<br />Following up my treaty rate thoughts, if I'm trying to get the lowest treaty payee (and rate) from a specific
listof payees for every possible payor country, the following seems to work, but is it right? I'm specifically
wonderingabout the group by clauses. (Or if there is a better way.) [table treaties has columns payor,payee,rate and in
thissample, I'm just trying to find which country payee between Ireland and the Netherlands has the lowest rate from
eachindividual payor country.] <br /><br />select a.payor,a.payee,a.rate<br />from treaties a,<br /><br />(select
payor,min(rentr)from treaties<br />where payee in ('Ireland','Netherlands') group by payor<br />) b<br /><br />where
a.payor=b.payor<br/>and a.rate = b.min<br />and payee in ('Ireland','Netherlands')<br />group by a.payor, a.payee,
a.rate<br/>