[broccoli]

I try to join 2 tables. First I thought it should be 1 to 1 tables, but then I've found out that the data on the tables is not very clean. In some cases it is possible that there are 2 or none matches. But I need just one of it (it doesn't matter which one).

My select:

select distinct fk_emp, .....
from table1 a
left outer join table2 b on a.key = b.key
where .....

but in some cases, I still get more than 1 result
what is wrong?
thanks

[Pat Phelan]

Can you explain what you are trying to do a little better? This sounds like a fundamental logic problem, so at least I need to understand what you are trying to do a bit better so that I can figure out how to help you do it.

-PatP

[broccoli]

an example:
I have a table with employees (table1) and an other table (table2) with informations like the department, branch .... and a foreign key to a third table where the PersonalID (which is the information I need) is. Now, it is possible that an employee works in more than just one department, so the join with table1 and table2 gives me more than 1 result. How can I join table1 and table2 so I get only one result?

table1
empno 1

table2
empno 1 department FINA Branch ZH FK 3
empno 1 department RISK Branch AG FK 3

table3
persID 3 persno 123456.....

[r937]

Originally Posted by broccoli

How can I join table1 and table2 so I get only one result?

the first thing you have to do is decide which one of the multiple table2 rows you want

the one with the lowest department name?

the one with the latest date?

the one with the shortest description?

etc.

then you use GROUP BY and MIN(), MAX(), etc.