PostgreSQL: Difficulty in Using LATERAL Join for Cross-Referencing Rows in a Single Table
I'm maintaining legacy code that I'm following best practices but I keep running into I'm working on a project and hit a roadblock. I'm trying to use a LATERAL join in PostgreSQL 13 to cross-reference rows within the same table based on a condition, but I'm running into unexpected behavior. I have a table called `employees` where I want to find pairs of employees who report to the same manager. The structure of the table is as follows: ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), manager_id INT REFERENCES employees(id) ); ``` I attempted the following query: ```sql SELECT e1.name AS employee, e2.name AS colleague FROM employees e1 JOIN LATERAL ( SELECT name FROM employees e2 WHERE e1.manager_id = e2.manager_id AND e1.id != e2.id ) e2 ON TRUE; ``` However, this query returns duplicates for some employees. For instance, if both Alice and Bob report to Carol, the output shows both pairs (Alice, Bob) and (Bob, Alice). I expected to only see one of these pairs. I've tried adding a `DISTINCT` keyword and filtering out duplicates, but that doesn't seem to resolve the scenario effectively. Is there a better way to ensure that I don't get mirrored pairs in the results? Should I adjust my LATERAL join logic or is there a different approach to achieve this without redundancy? Any insights would be greatly appreciated. The project is a service built with Sql. Is this even possible? Any pointers in the right direction? This is my first time working with Sql stable. For context: I'm using Sql on Windows 10. I appreciate any insights! This is my first time working with Sql latest. I'd be grateful for any help.