Query to select 1 unique value when using Join?

[losty]

Hey guys,

I have a somewhat complex problem that is giving me headaches. I have 2 tables (both have in excess of 1 million rows) and I want to effectively join them together. Using a small example below maybe you can help. Im starting with tables 1 and 2, and want to end up with table 3.



Field2(table1) and Field4(table2) are the same values (identical). However, because they have 2 identical values (i.e. 1000) when i try to do a join it makes too many duplicates. I basically want in the final table for Field 1 to only be present once (no duplicates) and the first time it calls a value from Field 3 to not duplicate that value (Identical values may be present but they will have different Field4s associated with them).

Table 2 has many more values than exist in table 1 and some need to be skipped over (i.e. the value 1002 in Table2 doesn't have a matching "L" value in Table1 and so can be ignored). An example of the results I dont want is shown in table 4 for reference purposes.

Many thanks!

Table 1

Field1	Field2
L1	1000
L2	1000
L3	1001
L4	1003

Table 2

Field3	Field4	Field5	Primary Key
4.5	1000	2000	1
3.4	1000	2000	2
2.4	1001	2001	3
1.7	1002	2002	4
1.4	1003	2003	5

Table3 - The results I want

Field1	Field2	Field3	Field5
L1	1000	4.5	2000
L2	1000	3.4	2000
L3	1001	2.4	2001
L4	1003	1.4	2003

Table4-The results I don't want where L1 and L2 become duplicated.

Field1	Field2	Field3	Field5
L1	1000	4.5	2000
L1	1000	3.4	2000
L2	1000	4.5	2000
L2	1000	3.4	2000
L3	1001	2.4	2001
L4	1003	1.4	2003

[orange]

This article may help
http://forums.aspfree.com/attachment...2&d=1201055452

Each row in a table should have some field that uniquely identifies that row.
Said differently - each row requires a unique identifier.

[losty]

So one way I did do this (sorry I should have mentioned) was that in table1 the unique ID is basically field 1. To generate a unique ID in Table2 I put in a Primary Key...