Hi,

I've got an worse day or something but I'm stuck. I've got two tables:

Table1
-------------
id Name
1 Barbara
2 John
3 Steve

Table2
-------------
id Name2
1 Wattson
2 Jackson
3 Henry
1 Smith



I want to get all rows from table1 and appropriate to id, Name2 from table2. Joined are table1.id and table2.id. Problem is that row with id = 1 is duplicated. I tried:

SELECT a.id, a.Name, b.Name2 FROM Table1 AS a
LEFT OUTER JOIN Table2 AS b ON a.id = b.id

But it doesnt work as I want - I get:

1 Barbara Wattson
2 John Jackson
3 Steve Henry
1 Barbara Smith


Any idea ?

--
Marcin Domaslawski

that looks okay to me

you forgot to mention which way you actually wanted it to work

Ok - I want that there wouldn't be the last row - with id = 1 -> because it's duplicated (1st and 4th). Should be only 3 rows:

1 Barbara Wattson
2 John Jackson
3 Steve Henry

It's relation table1 to table2 like 1 to 0, 1 or many but I care only about that 1st found rec. from table2 appropriate to row from table1.

sorry, there is no such thing as "first found row"

next best thing is the minimum or maximum value...

Yes this is it - there isn't first found row ...
and YES this is it ... what I wanted

I've never used MAX in this way

Thanks

I think you have a bigger problem than what you are working on. What happens if you get an entry in table 2 of "1,Young"? Do you still want Watson or would you now want Young? Also, how do you know your user wouldn't want the others. Typically, you'd want to keep a persons entire name in the same table rather than trying to join it up this way. Or if you were really out to save on disk space you could go with three tables, such as:

table1 FIRSTNAMES
FN_ID, F_NAME
(1,ANN
2,BARBARA
3,JOHN)

tABLE2 LASTNAMES
LN_ID, L_NAME
(1,WATSON
2,SMITH
3,JACKSON)

TABLE3 NAME
N_ID, FN_ID, LN_ID
(1,1,1
2,1,3
3,2,1)
This last table would equate to:
ANN WATSON
ANN JACKSON
BARBARA WATSON

I forgot to mention in my earlier post. If you were going with the three table solution that I spoke of, I would suggest two unique indexes on the first and last name tables. One for the ID number as you DO NOT want to end up with what you have already(not knowing which name should be used). The other on the name itself, reason being you are attempting this solution, so as to not duplicate a name, so ensure that you do not duplicate it. Without those indexes on both tables you would just be setting yourself up for data errors down the road. If that were to happen, then Vincent McBurney or one of those other data quality people would be hunting you down.