October 19, 2005 at 8:11 am
1 - Select ownerID from dbo.Houses GROUP BY OwnerID HAVING COUNT(*) > 1
2 - What do you need exactly??
October 19, 2005 at 9:28 am
a)
select ownerid, count(ownerid) from
(select distinct ownerid, ownerfname, ownerlname
from house)
group by ownerid
having count(ownerid) > 1
b)
##I think this is close:
## the intent is to just select the records from HOUSE that aren't in the
## above query
select *
into cleanhouse
from house h
where not exists (select x.ownerid from
(select distinct ownerid, ownerfname, ownerlname
from house) x
group by x.ownerid
having count(ownerid) > 1
)
Another way is to split off the OwnerFName, OwnerLName and OwnerID fields into a separate table:
select distinct ownerid, ownerfname, ownerlname into owners
from house
Then, drop the OwnerFName and OwnerLName fields from the House table, and just do your analysis on the Owners table.
Then, adding a Unique constraint on the OwnerFName+OwnerLName fields (I'm assuming you might have some other way of uniquely identifying two different John Smith's, for example).
Then, a little referential integrity would keep the House (and Owners) tables pretty well clean on their own.
October 21, 2005 at 1:26 am
a)
select ownerfname, count(ownerid)
from house
group by ownerfname
having count(ownerid) > 1
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply