ST Alpha - "TFW you send 60 minutes wrestling with a SQL query and you're still not even sure why it works now (geek overload)" (Reading a Post)

SlickerTalk
Search Archives
The Leaderboard
The FAQ

Down to Post

Backboards:

Posts: 164

TFW you send 60 minutes wrestling with a SQL query and you're still not even sure why it works now (geek overload)

Posted by oblique (aka kkuphal) Nov 5 '21, 07:46

Trying to filter out Tickets that have a parent (IE: subtasks). Because of how the system I'm working with works, I have to select from TABLE but The "Parent ID" is in another table so do a quick:

SELECT * FROM TABLE WHERE ID NOT IN (SELECT PARENT_ID FROM OTHERTABLE)

This should give me IDs which are NOT set as Parent_IDs, right?

Doesn't work.

SELECT * FROM TABLE WHERE ID NOT IN (SELECT ID FROM OTHERTABLE WHERE PARENT_ID IS NOT NULL)

This does.

Responses:

You are correct in that the first one yields a list of IDs which are not themselves Parent_IDs, but that's not what you want. - Cowboy BeBaron Nov 5, 08:33
on the first one your join is on id to parent_id. "where id not in [parent_id's]". And wouldn't a left join work? - JaxSean Nov 5, 08:03 4
- it's a weird limitation of the system I'm working with - oblique Nov 5, 08:18 2
  - ah. It could be because there's blanks (non-NULLs) so it joins on those? dunno. -- nm - JaxSean Nov 5, 08:23 1
    - I suspect it some weird thing with uniqueidentifiers because that's what those IDs are - oblique Nov 5, 08:29
- Yep, that checks out to me -- nm - Beryllium Nov 5, 08:17

Post a message top

TFW you send 60 minutes wrestling with a SQL query and you're still not even sure why it works now (geek overload)

Replies are disabled on threads older than 7 days.