Self Join

[sonia.sardana]

Hey see the pic,how the query is returning 5 records,Can somebody explain me?

[Pradeep1210]

What you wrote is actually a cross join with a condition.
So see it this way:
Each record of Employee F will have a match in with each row of Employee S and vice versa. And then records in which the country of Employee F is not same as country of Employee S would be removed. What you would be left is these 5 matches.

If you use the DISTINCT keyword, duplicate rows will be removed.
I would also advice you to use inner joins instead of cross joins. Inner joins are much better performance wise.

[Pradeep1210]

(not tested code)

Select F.EmpId, F.LastName, S.EmpId, S.LastName, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country

[dee-u]

To get the unique values you can also try it like this.

Select F.EmpID,F.LastName,S.EmpID,S.LastName,F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country AND F.EmpID = S.EmpID AND F.LastName = S.LastName

[leinad31]

What output did you expect or need?