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. (Obviously I could also run into someone
askingme for more than 2 payees and asking for the combination payor/payee with the lowest rate for each individual
payor).<br /><br />Thanks,<br /><br />Bryan<br />