[Answer]-SQL to detect inconsistency between 1:M and M:M relations

by

1👍

✅

There are different approaches to this. For your case, I would use not exists:

SELECT o.uuid 
FROM myapp_salesorder o JOIN
     myapp_customer c
     ON o.customer_id = c.uuid
WHERE NOT EXISTS (SELECT 1
                  FROM myapp_customertousergroupmembership m 
                  WHERE m.customer_id = c.uuid AND m.userGroup_id = o.userGroup_id
                 );

For optimal performance, create a compound index on myapp_customertousergroupmembership(customer_id, userGroup_id).

EDIT:

The join version is easy enough:

SELECT o.uuid 
FROM myapp_salesorder o JOIN
     myapp_customer c
     ON o.customer_id = c.uuid LEFT JOIN
     myapp_customertousergroupmembership m
     ON m.customer_id = c.uuid AND m.userGroup_id = o.userGroup_id
WHERE m.customer_id IS NULL;

I don’t know if the performance will be any better, though.

👤Gordon Linoff

Leave a comment