query help 1

[MartDawg]

I have a table that looks like this...

SomeTable
userID(int)
somedate(datetime)
somethingelse(varchar)

There is no unique field currently. I want to make the first two columns together the primary key(userID, somedate).

When I do this I get an error for primary contraint.

I relize that there must me duplicate records with same userID and somedate.

How can I query this table to pull those dups out so I can change them to make my necessary change?

For some reason I'm having a brainfart on this. This is what I've tried...

Select TS.userID, TS.somedate from SomeTable as TS where (select * from SomeTable as TD where TS.userID = TD.userID and TS.somedate = TD.somedate)

This seems to bring back all records though.

 

[amorous]

Try this:

Select userID,somedate from Yourtablename group by userID having Count(userID)>1

-VJ

 

[MartDawg]

I get an error about having somedate not in the group by clause.

 

[amorous]

Try this:

Select userID from Yourtablename group by userID having Count(userID)>1


this will give all the userID's which occur more than once in your database


-VJ

 

[MartDawg]

I want a little bit more than that though. I want the userid's that occur more than once, only if they have the same datetime?

 

[amorous]

Try this:

Select t1.userID from Yourtablename t1
Inner join yourtablename t2
on
t2.userid=t1.userid
Where t2.userid IN (SELECT t2.userID FROM yourtablename WHERE t2.somedate=t1.somedate group by t2.userID having Count(userID)>1 )


-VJ