Thanks. I tried also the below code 2 which works great. But the problem of both (yours and mine solution) is that I need only unique users. I tried this:
SELECT users.id, users.firstName, users.lastName, k2.whom
(SELECT DISTINCT users.id
but in this way I get error Unknown column 'k2.whom' in 'field list' because I selected DISTINCT users.id. But I also need to show via who each person is related. So I need to pull from users information from whom and through1.
I think the best solution will be to create temporary table. Group by is too slow. Or is there any other solution?
And this is a code which is also solution to my previous problem:
k1.who AS who,
k2.who AS through1,
k2.whom AS whom
FROM (users INNER JOIN knows AS k2 ON users.id=k2.whom)
LEFT JOIN knows AS k1 ON k1.whom=k2.who