CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 273

SQL NOT IN results not what expected

I have the following query:

  SELECT om.member_id
    FROM org_member om
   WHERE om.org_id = 1
     AND om.member_id NOT IN (
         SELECT member_id 
           FROM org_group_member 
          WHERE member_id = om.member_id AND is_pending = 'N') 
ORDER BY om.member_id

The subquery within the NOT IN is returning no rows for a particular member id when run by itself outside of the full query. However, when the full query is run the subquery is not causing the member id to be excluded from the result set, even though no rows are returned by the subquery when run by itself.

For instance, if om.member_id of 1 is used in the subquery as a standalone query no rows are returned. Yet member id of 1 is returned if the full query is run.

I am clearly missing something with regard to the NOT IN clause.

Table org_member has: org_id member_id

Table org_group_member has: org_id grp_id member_id is_pending (Y or N)

I need a result set of all members that do not exist in the org_group_member table or are only in the org_group_table with is_pending = Y (for which a member could have multiple rows since the member could be in multiple groups)

e.g org_group_member
org_id  mbr_id  grp_id  is_pending
1       1       1       Y
1       1       2       Y
1       2       1       N
1       2       2       Y

My result set should have member 1 and 3 but not 2. 3 should be included because it does not exist in the org_group_member table

Upvotes: 0

Views: 90

Answers (6)

William Rende
William Rende

Reputation: 1

Try this query.

SELECT om.member_id
    FROM org_member om
    left join org_group_member ogm 
    on om.member_id = ogm.id_menber
   WHERE om.org_id = 1
   and ogm.is_pending = 'N'
   ORDER BY om.member_id

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

SELECT om.member_id
FROM org_member om 
     JOIN org_group_member ogm ON ogm.member_id=om.membeer_id
WHERE om.org_id = 1
  AND ogmis_pending = 'N' 
ORDER BY om.member_id

Guess you need just not pending member ids

about your query if the subquery returns empty the result is true e.g. 42 not in {}

UPDATE: according to new requirements

SELECT om.member_id FROM org_member om LEFT JOIN org_group_member ogm ON ogm.member_id=om.membeer_id WHERE om.org_id = 1 AND (ogm.is_pending = 'N' OR ogm.member_id is null) ORDER BY om.member_id

Upvotes: 0

Shailesh Pratapwar
Shailesh Pratapwar

Reputation: 4224

You do not need a corelated sub query.

Following query should work.

SELECT om.member_id
    FROM org_member om
WHERE om.org_id = 1
    AND om.member_id NOT IN (
        SELECT member_id 
        FROM org_group_member 
        WHERE is_pending = 'N') 
ORDER BY om.member_id

Upvotes: 0

Galcoholic
Galcoholic

Reputation: 518

Maybe you have records in ORG_MEMBER with MEMBER_ID=NULL? In this case a NOT IN will be true.

Upvotes: 0

Jim Wright
Jim Wright

Reputation: 6058

In your example you state that the subquery returns no rows for a member_id of 1. This means that when you use NOT IN, you are essentially asking for any rows that aren't in an empty data set, so you will get everything.

If your subquery returned a row for member_id of 1, then the full query would be missing results for member_id 1.

Upvotes: 0

Shuddh
Shuddh

Reputation: 1970

Remove the member_id = om.member_id from the where clause. Its contrasting the NOT IN clause.

Upvotes: 1

Related Questions