One - Many Relationship 1

[Zurich98]

Hello All,


My envinronment is SQL 2005. The data in my table is as follow:

ID Test
1 Tax Exempt
1 Tax Composition

I would like to pull only one Test for this ID. It does not matter which test. I don't have a date field or any other unique key to differentiate between the two test for this ID

select *
from student s
left join Test t
on s.ID = t.ID

result:

1 John Doe Tax Exempt
1 John Doe Tax Composition

desire result:

1 John Doe Tax Exempt

or

1 John Doe Tax Composition

Any input/suggestion is greatly appreciated. Thanks

 

[gmmastros]

You could use the MIN of the Test....

select *
from   student s
       left join (
           Select Id, Min(Test) As Test 
           From   Test 
           Group By Id) t
         on s.ID = t.ID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Zurich98]

I was about to go the long way by using identity(smallint,1,1) as RowNmbr and then pick the min or max by RowNmbr. I didn't realize I can use min and max function on text field. Someone else in another forum suggests the same idea. Thank you for your help.