We have Microsoft SQL Server 2005.

I have been having a hard time conceptualizing what I need to do.

We have fields called loan, loan_ref, and property.

The loan_ref can have one or more loans.

A loan can have one or more properties associated with it.

A property can be associated with one ore more loans.

(1) I need to do one thing if a loan has more than property.

(2) Another thing if a property has more than one loan.

(3) And a different thing if there are multiple loans with multiple loans.

Note, recall that loan_ref can have one or more loans. I think that means I should let that be my focus in helping to identify those buckets.

What I cannot conceptualize is how to identify the three buckets mentioned above.

Once I figure that, I can then move onto doing what needs to be done for each of the three scenarios mentioned.

Thanks

Perhaps you can post some example data and what you want to do but I guess you are just wanting to some come grouping.

Select loan_ref, count(property)
from table
group by loan_ref

Simi

Thanks Simi,

Here are some samples

(a) one loan, one property
loan loanref property
3333351 3333351 RINK
3343403 X000111 FIFTH

(b) one loan, > one property
loan loanref property
3333346 3333346 CENTER1
3333346 3333346 CENTER2

(c) one property, > one loan
loan loanref property
3333081 3333081 PARK1
3333376 3333376 PARK1
3343384 X000144 MAIN
3327942 X000144 MAIN

(d) > one loan, > one property
loan loanref property
3343406 X000135 FLAGI
3343408 X000135 FLAGII
3343998 X000174 MAPLE
3343998 X000174 EAST
3343998 X000174 MERKER
3343106 X000174 MAPLE
3343106 X000174 EAST
3343106 X000174 MERKER

Using the above, (a) is staight forward, because that is like doing a simple query.

My issue is with being able to identify (b), (c), and (d).

After I identify each of those 3 buckets, I need to do something with them.

I need to first, however, know how to put them in the right bucket.

Thanks

I believe I have figured it out.

It may not be the best way to do it, but I believe it worked.