Query nodes without a specific relationship and a property in one of those nodes

[janezic]

Hello Neos,

I have two different types of nodes: (:Customer) (with an id) and (:Invoice) (with a string_id which contains the year of the issuance of the invoice). Those two types of nodes are related via a [:INV_FOR_CUST].

I would like to find those customers for which no invoice containing the current year in the inv_string_id has been issued.

How could I create a query for those?

Best regards,

JJJ

[ameyasoft]

Try this:
MATCH (a: Invoice) where a.string_id  contains("2022") 
and not (a)-[:INV_FOR_CUST]-(:Customer)
RETURN a

[glilienfield]

@ameyasoft approach is correct, but the solution mistakenly returns the invoice, not the customer as required. Rearranging @ameyasoft solution should give you the list of customers.

MATCH(c:Customer)
MATCH(i:Invoice WHERE i.string_id contains "2022") 
WHERE not exists ( (i)-[:INV_FOR_CUST]->(c) )
RETURN c.id

[janezic]

Good Morning,

Thank you for your trials but I had tried them out before, but they are not working properly. So what I did is:

• I created a list of all customer-ids for customers for which an invoice with "2022" was issued.
• I then took all customers who where not in this list.

MATCH (c:Customer)<-[INV_FOR_CUST]-(inv:Invoice)
WHERE inv.inv_id_strg CONTAINS "2022"
WITH DISTINCT c.cust_id as cust_id
ORDER BY cust_id ASC
WITH collect(cust_id) as c_list
MATCH (c1:Customer)
WHERE NOT c1.cust_id IN c_list
MATCH (c1)-[:HAS_CUST_STATE]-(c1s)
WHERE NOT (c1s)<-[:HAS_CUST_STATE_PRED]-(:Customer_State) AND c1s.cust_status = 1 AND (c1s.cust_no_invoice = FALSE OR c1s.cust_no_invoice IS NULL) 
RETURN c1, c1s

This now works.

Best regards,

JJJ