SQL Query help: co-workers and employees from one query I have two tables defining users and user employments. The user table looks like this:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `fullName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
);


and the employments table:

CREATE TABLE `user_employment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employer_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `user` (`id`)
);


A user can employ users, but can also be employed. I need one query that can get all users that is a co-worker of a given user. I have tried with this query, but it only gives me employees for employer, or employer for employees:

    select u.id, u.username, u.fullName
      from user u
 left join user_employment e1 on e1.employee_id = u.id
 left join user_employment e2 on e2.employer_id = u.id
     where (e1.end &gt;= NOW() or e1.end is NULL)
       and (e2.end &gt;= NOW() or e2.end is NULL)
       and (e1.employee_id = &lt;user id&gt;
            or e1.employer_id = &lt;user id&gt;
            or e2.employee_id = &lt;user id&gt;
            or e2.employer_id = &lt;user id&gt;)
           and (e1.status = "confirmed"
                or e2.status = "confirmed")
               and u.id &lt;&gt; &lt;user id&gt;
  group by u.id;


If anything is unclear, please ask!

Thanks.
select u.id, u.username, u.fullName
from user u
join user_employment e1 on e1.employee_id = u.id
join user_employment e2 on e2.employer_id = e1.employer_id
where (e1.end &gt;= NOW() or e1.end is NULL)
       and (e2.end &gt;= NOW() or e2.end is NULL)
       and e2.employee_id = 2
       and e1.status = "confirmed"
       and e2.status = "confirmed"
       and u.id &lt;&gt; 2
group by u.id;


http://sqlfiddle.com/#!9/b13e6/4

Find the employer of a given user and then find all (other) employees of that employer. Do not use left joins unless you need them (you do not in this case).