Sandeep,

We run into this problem a lot. While trying to make our database actually handle variations in the
data, we often found ourselves with N addresses, N names, N ... our users, for some reason, keep
thinking there should only be 1 of anything (unless, some day, they wish otherwise -- but it never
lasts.)

We've had a few solutions -- either deal with the speed of the query (by using a correlated sub-
select that uses 'first 1 ...' either to get the data, or get the ID of the row, then match the
rest of the row to the one and only one ID retrieved) or we update the table. When doing a sub-
query, remember that at least in some versions (we still use 1.0x) you can't use an 'order by' in
the sub-query. If you just want to pick any random dependent record, that works fine ... but it
didn't for us. We've had two solutions come up:

- the update solution: we have a trigger on the dependent tables that updates the master record
with a FK pointing back to the child record of highest importance (name to show on reports, address
to show on labels.) as it's in a trigger, it can do an 'order by' just fine on its one little query
to find the most 'current' record, and set the FK in the master record. that works for us. when we
want that record, we just join by the FK. when we want it all, we join with the other FK.

- the sub-query solution: one of our programmers wound up created a stored procedure to find
the 'most useful' record id, given the id of the parent record ... it was for a report of some
sort, i think. it wasn't fast, but it got around the lack of 'order by' in the sub-query, by
outsourcing it to a stored procedure. as i recall, he wanted several fields from that dependent
table, so he joined to the dependent table, and the join-clause included something like "and
sub_record.id = most_useful(parent_record.id)".

this kind of query is never fun, and most of the time, users come back later wanting the rest of
the data. (but only after they find the exception that created the 1:N case in the first place and
ask us why stuff doesn't add up.) i wish you much luck!

-philip