Re: Sql select question

From: andy vandenberghe <hp275_removethis_at_skynet.be_removethis>
Date: Fri, 21 Nov 2003 18:23:06 +0100
Message-ID: <hp275_removethis-69E8CA.18230521112003_at_news.skynet.be>

Hi VC,

Thanks, your code worked allright; here's the real-world translation.

/* list all accounts where the count is not equal */ select a.voucher_no, a.ext_inv_ref, a.count_700500, b.count_400 from (select voucher_no, ext_inv_ref, count(*) count_700500 from agltransact
where client='MG'
and account=700500

and voucher_type in('P4','P5')
and voucher_no >= 63000020
and voucher_no <= 63000024

group by voucher_no, ext_inv_ref) a,
(select voucher_no, ext_inv_ref, count(*) count_400 from agltransact
where client='MG'
and account in('400400','400500','400301','400300')

and voucher_type in('P4','P5')
and voucher_no >= 63000020
and voucher_no <= 63000023

group by voucher_no, ext_inv_ref) b
where a.ext_inv_ref = b.ext_inv_ref
and a.voucher_no=b.voucher_no
and a.count_700500 != b.count_400;

Hrgds,
andy

In article <C5Tub.191428$275.658505_at_attbi_s53>,  "VC" <boston103_at_hotmail.com> wrote:

> Hello andy,
>
> Given:
>
> create table t1(ex_inv_ref int, account int);
>
> the simplest way to do what you want is:
>
> select a.ex_inv_ref, count_6040, count_1512 from
> (select ex_inv_ref, count(*) count_6040 from t1 where account=6040 group
> by ex_inv_ref) a,
> (select ex_inv_ref, count(*) count_1512 from t1 where account=1512 group
> by ex_inv_ref) b
> where a.ex_inv_ref=b.ex_inv_ref and count_6040 != count_1512;
>
> Rgds.
>
Received on Fri Nov 21 2003 - 18:23:06 CET