I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an array and need to determine if these records could be deleted. This is just used for display, no actual deleting is taking place here.
Based on this answerBased on this answer, I can find out if one record can be deleted:
(SELECT 1 FROM case_contacts WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM case_payments WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM invoices WHERE contact_id = 5000 LIMIT 1)
-- etc
LIMIT 1;
I could run this query for every contact ID I'm given, but that seems very inefficient.
Is there a way to send a single query for multiple contact IDs (up to ~500 at once)? Any kind of result would be fine, including only the IDs that can be deleted, or only the IDs that cannot be deleted, or a result set of (contact_id, can_be_deleted).
EDIT: This is what I'm using until I find a more efficient solution:
SELECT c.id
FROM contacts c
WHERE c.id IN (1,10,20,1557,5000,15057)
AND NOT EXISTS (SELECT 1 FROM case_contacts WHERE contact_id = c.id)
AND NOT EXISTS (SELECT 1 FROM case_payments WHERE contact_id = c.id)
AND NOT EXISTS (SELECT 1 FROM invoices WHERE contact_id = c.id);
