Advance TSQL question

Hi,
I would like to find customers (same id) with different addresses:
Id Address
-- --
1 123 ABC
2 456 DEF
1 789 GHI
2 456 DEF
3 000 XYZ
As above example: my result would be Id=1.
Thanks in advanced,
Culamselect id, count(id) cnt from (select distinct id, address from tablename)
group by id having count(id) > 1
"culam" wrote:

> Hi,
> I would like to find customers (same id) with different addresses:
> Id Address
> -- --
> 1 123 ABC
> 2 456 DEF
> 1 789 GHI
> 2 456 DEF
> 3 000 XYZ
> As above example: my result would be Id=1.
> Thanks in advanced,
> Culam|||On Fri, 3 Feb 2006 03:34:15 -0800, culam wrote:

>Hi,
>I would like to find customers (same id) with different addresses:
>Id Address
>-- --
>1 123 ABC
>2 456 DEF
>1 789 GHI
>2 456 DEF
>3 000 XYZ
>As above example: my result would be Id=1.
>Thanks in advanced,
>Culam
Hi Culam,
Try:
SELECT Id
FROM YourTable
GROUP BY Id
HAVING MIN(Address) <> MAX(Address)
Hugo Kornelis, SQL Server MVP