|
benricho
Yak Posting Veteran
Australia
84 Posts |
 Posted - 06/07/2005 : 04:22:11
|
I had a hard time coming up with a good subject for this. I think the best way for me to describe the problem is to jump straight into the DDL.
CREATE TABLE AnimalType (
[ID] int primary key,
[Name] varchar(20)
)
GO
INSERT INTO AnimalType VALUES (1, 'Dog')
GO
INSERT INTO AnimalType VALUES (2, 'Cat')
GO
CREATE TABLE Staff (
[ID] int primary key,
[Name] varchar(20),
AnimalPreference int FOREIGN KEY REFERENCES AnimalType([ID])
)
GO
INSERT INTO Staff VALUES (1, 'Ben', 1)
GO
INSERT INTO Staff VALUES (2, 'Steve', 1)
GO
INSERT INTO Staff VALUES (3, 'Greg', 1)
GO
INSERT INTO Staff VALUES (4, 'Dave', 2)
GO
In the results I want random staff members, but only 1 for each different AnimalType.
So possible results could be
1, Ben, 1
4, Dave, 2
Or
3, Greg, 1
4, Dave, 2
The only way I can think of doing this would be to use a cursor to get the unique animal types, then loop through selecting random staff (using order by newid()), and adding these staff to a temp table, and selecting the entire temp table to get the results.
But I get the feeling that there is a better way to do this that I just can't see. Any ideas? |
|
Help selecting records with a unique column
