Hello All,
The below is my database:
I have the below question:
Return the name of Students studying in the Location Limerick (as Limerick_Student) and the names of Students they are FRIENDS_WITH (or are FRIENDS_WITH them), but who do not study a course provided by Limerick.
Results should be sorted alphabetically by student name, and within that by alphabetically by friend name.
I tried everything what I can think of but still getting wrong results.
This is my query:
MATCH(s:Student)-[:STUDIES]->(c:Course)-[:PROVIDED_BY]->(l:Location{location:"Limerick"})
MATCH(s1:Student)-[:FRIENDS_WITH]-(s:Student)
WHERE NOT((s1:Student)-[:STUDIES]->(c:Course)-[:PROVIDED_BY]->(l:Location{location:"Limerick"}))
RETURN DISTINCT s.name AS Limerick_Student, s1.name as Non_Limerick_Friend
ORDER BY s.name, s1.name
I'm wondering if someone can advise how this query should look like.
Thank you,
Anna
I do think the approach looks correct. If there is a problem with the results it’s because you are reusing the variables where they should not refer to the existing entities.
Try this:
MATCH(s:Student)-[:STUDIES]->(:Course)-[:PROVIDED_BY]->(l:Location{location:"Limerick"})
MATCH(s1:Student)-[:FRIENDS_WITH]-(s)
WHERE NOT EXISTS((s1)-[:STUDIES]->(:Course)-[:PROVIDED_BY]->(l)
RETURN DISTINCT s.name AS Limerick_Student, s1.name as Non_Limerick_Friend
ORDER BY s.name, s1.name
Hi Gary,
Thank you for your quick reply.
Your query still doesn't give me the correct answer, but it's closer to the correct one than in the last two weeks. I will try to play with your query, maybe I will succeed.
Thank you once again,
Anna
Do you have test data I can use? What was incorrect about the results?
Hi Gary,
I have raw data, but can't upload it here for some reason.
The results were slightly different than expected, although way better than when I tried with my query.
Anna
Do you want just immediate friends or are you expecting friends of friends as well?
Try this:
MATCH(c:Course)-[:PROVIDED_BY]->(l:Location{location:"Limerick"})
MATCH(s:Student)-[:STUDIES]->(c)
MATCH(s1:Student)-[:FRIENDS_WITH]-(s)
WHERE NOT EXISTS((s1)-[:STUDIES]->(c)
RETURN DISTINCT s.name AS Limerick_Student, s1.name as Non_Limerick_Friend ORDER BY s.name, s1.name
I assume the two students could take the same course 'c', but from different locations. This query would not allow that.
I used the test data you send via email. The results from my query above matches exactly with the expected results in the table included in your email.
MATCH(s:Student)-[:STUDIES]->(:Course)-[:PROVIDED_BY]->(l:Location{location:"Limerick"})
MATCH(s1:Student)-[:FRIENDS_WITH]-(s)
WHERE NOT EXISTS((s1)-[:STUDIES]->(:Course)-[:PROVIDED_BY]->(l))
RETURN DISTINCT s.name AS Limerick_Student, s1.name as Non_Limerick_Friend
ORDER BY s.name, s1.name
Query results:
Expected results:
What are you seeing that is wrong when you run the query?
