Q: Distinct Join???

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

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
 
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!
***************************
 
Thanks Gregory

Cowboy (Gregory A. Beamer) - MVP said:
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!
***************************
Click to expand...
 
Back
Top