sqlencryptionneo4jcyphertranslate

SQL outer join vs Neo4J Cypher


I am new in Neo4J Cypher, I know what I want to get in SQL, but can't create query in Cypher.

Let's have 3 tables:

Pers(persId,name,workId,born)
Work(workId,name)
Friend(pers1Id,pers2Id)

Problem: find people, who work in the same company and are not friends and age difference is less than5. In SQL a simple query looks like below:

select * from Pers p1 join Pers p2 on p1.workId=p2.workId 
where not exists
(select 1 from Friend f where p1.persId in (f.person1Id,f.person2Id) 
and p2.persId in (f.person1Id,f.person2Id) ) 
and abs(p1.born-p2.born)<5

what about Neo4J Cypher? Any help will be appreciate, especially how to translate SQL to Cypher queries. Mirek


Solution

  • Let's assume you have :Person and :Workplace labels. Let's assume these kinds of relationships already exist:

    (:Person)-[:WorksAt]->(:Workplace)
    (:Person)-[:FriendsWith]->(:Person)
    

    Your Cypher query would look something like:

    MATCH (p1:Person)-[:WorksAt]->()<-[:WorksAt]-(p2:Person)
    WHERE NOT (p1)-[:FriendsWith]-(p2)
    AND abs(p1.born-p2.born) < 5
    RETURN p1, p2
    

    Though keep in mind I think you will get two rows per pairing, with the order switched (we can eliminate that without much trouble if you need to).

    In general, it's a good idea to draw out a diagram of the pattern or query, and from there it's usually not too hard to translate that to Cypher.