Jeremy Fisher Jeremy Fisher - 1 month ago 8
MySQL Question

Joining a table with itself vs using Group By MySQL

Here is a simplified version of my table:

It contains the columns

employee_id column2 column3 x y

and they are all
NUMERIC


I am having trouble writing a complicated MySQL query. I want to get all the
employeeid
and
column3
values for all employees that have the same value in
column2
as another employee and have DIFFERENT values for
x
and
y
PAIR from every other employee. For example, if the following 4 rows were in table:

2 100 123.456 5 7
1 234 123.456 5 7
3 100 456.789 5 10
4 100 123.456 5 7


The rows
2 100 123.456 5 7
and
3 100 456.789 5 10
should be obtained because they have different employee ids (
2
vs
3
), the same value for
column2
(
100
and
100
), and different
x
,
y
pair: (Employee 2 has
x
= 5 and
y
= 7, which is distinct from
x
= 5 and
y
= 10).

How can I compare the documents of table with other documents within itself?

Answer
SELECT e1.*
FROM employee e1
JOIN employee e2
  ON e1.employee_id <> e2.employee_id  
 AND e1.column2 = e2.column2 
 AND e1.X <> e2.X
 AND e1.Y <> e2.Y

But I guess your last condition should be

AND (e1.X <> e2.X OR e1.Y <> e2.Y)