"Geoff Jones" wrote:

> Hi
>
> I'm hoping that somebody can help me with the following:
>
> I have two tables, each of which has its own field holding integers. I would
> like to generate a list of the integers that the two fields have in common
> (without duplicates). So, for example, if the first table had a list
> 1,2,3,4,5,6,7,7,7,8,9 and the second table had 2,2,3,4,7,8 then I'd like to
> get the list: 2,3,4,7 and 8.
>
> I've managed to do this using Access i.e. a distinct join, but would like to
> achieve the same thing using a DataRelation. So far, I've created a
> datarelation between the two fields of the two tables but am not sure how to
> proceed.
>
> Can anybody help?
>
> Thanks in advance
>
> Geoff
>
>
>

"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:E3DC5554-B11E-4456-BB12-C4B0CB8E7864@microsoft.com...
> Sometimes the question is not "can you" but "should you". Realistically,
> the
> database is best at answering questions like this. You can certainly pull
> information into your application tier and sort through the records to
> find
> uniques, but it is a rather inefficient use of resources, as a DISTINCT
> with
> a UNION can easily sort out single instances of a specific number. In
> order
> to accomplish the same in .NET alone, you will end up looping through
> data.
>
> If you need both a) the data and b) the aggregate, consider pulling
> multiple
> tables of information to your application tier. Create the related data in
> two tables and the aggregate data in a third. You can then answer all of
> the
> questions you might have without taking a perf hit for the aggregate.
>
> Hope this helps.
>
> ---
>
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
> "Geoff Jones" wrote:
>
>> Hi
>>
>> I'm hoping that somebody can help me with the following:
>>
>> I have two tables, each of which has its own field holding integers. I
>> would
>> like to generate a list of the integers that the two fields have in
>> common
>> (without duplicates). So, for example, if the first table had a list
>> 1,2,3,4,5,6,7,7,7,8,9 and the second table had 2,2,3,4,7,8 then I'd like
>> to
>> get the list: 2,3,4,7 and 8.
>>
>> I've managed to do this using Access i.e. a distinct join, but would like
>> to
>> achieve the same thing using a DataRelation. So far, I've created a
>> datarelation between the two fields of the two tables but am not sure how
>> to
>> proceed.
>>
>> Can anybody help?
>>
>> Thanks in advance
>>
>> Geoff
>>
>>
>>