EmmyS EmmyS - 2 months ago 7
MySQL Question

Return records that do not have a value in a certain field

I have a Jira system that we need to do some user cleanup for. I'm blanking out on how to write this particular query. The table in question looks like this, where CHILD_ID is a username and PARENT_ID is a group name.

CHILD_ID | PARENT_ID
=============================
Jane admins
Jane users
John users


So there are multiple records per person; one for each group they belong to. I need to return a list of CHILD_ID values that don't have a record for the admin group - so for the example, I'd want to return John only.

I've tried
select child_id from my_table where parent_id <> admins
, but that still returns the Jane record for the users group. I know this is something I've done before, but can't seem to remember how, and can't even figure out how to word a google search for this.

Answer

If I understand your question right, you could simply use an in query:

SELECT CHILD_ID FROM <your_table> WHERE CHILD_ID NOT IN (SELECT CHILD_ID FROM <your_table> WHERE PARENT_ID = 'admins');
Comments