"SteveInBeloit" <SteveInBel***@discussions.microsoft.com> wrote in message
news:A1694D77-D84D-4374-B8E2-DC3C91B251B6@microsoft.com...
> In a stored proc, I have a table that may have one to many rows.  I need to
> alert the users is one of the column of this table does not contain the same
> value for all rows.  Say the column is 10 for the first row, 5 for the
> second, and 10 the the remaining 3 rows.  I just need to tell them that there
> is a difference.
> I can't quite get a Select statement that will tell me this.
>
> Any thoughts?
>
> Thanks

"Daniel Crichton" wrote:

> SteveInBeloit wrote  on Thu, 10 Nov 2005 08:53:03 -0800:
>
> > In a stored proc, I have a table that may have one to many rows.  I need
> > to alert the users is one of the column of this table does not contain the
> > same value for all rows.  Say the column is 10 for the first row, 5 for
> > the second, and 10 the the remaining 3 rows.  I just need to tell them
> > that there is a difference.
> > I can't quite get a Select statement that will tell me this.
> >
> > Any thoughts?
>
> Use MIN and MAX and check for a difference. Assuming that your data
> integrity is fine (ie. no primary keys in the table with the many rows that
> do not exist in the parent table), try something like:
>
> SELECT [Key] FROM [FKTable] GROUP BY [Key] HAVING (MIN([Value]) -
> MAX([Value])) <> 0
>
> where [Key] is your primary key (replace with multiple columns if
> necessary), and [Value] is the column you need to have only one value in
> every row for each unique key.
>
> this should list all values in the column [Key] where there is more than one
> [Value]. However, note that it will ignore NULLs, so if there's a chance of
> those too you'll have to modify it to use COALESCE or ISNULL to convert NULL
> to a value that you do not expect to see.
>
> Dan
>
>
>