CODEWITHSUNDEEP
mysqlsqljoinleft-joinwhere-clause
Betty Mock
Betty Mock

Reputation: 1403

MySql tricky conditional in joined tables

The first table has several fields but "PersonId" is the only one of interest. The second table has "PersEmId" and "PersEmGr".

The mysql is:

   select  a.PersonId, b.PersEmId, b.PersEmGR
       from PersonRepDb a left join PersEm b 
       on a.PersonId = b.PersEmId
    (conditions to be figured out)

with no conditions the output is:

1   ABBOT.LE00  ABBOT.LE00  betty  
2   ABBOT.LE00  ABBOT.LE00  flutes  
3   ACKERBRO00

so we see ABBOT.LE is in two groups 'betty' and 'flutes', whereas ACKERBR000 is in no groups.

If the condition is: where PersEmGr = 'flutes' the query returns only ABBOT.LE00, which is correct.

However, I want to know who is NOT in 'flutes'. If the condition is 

where PersEmGr != 'flutes' OR PersEmGr  IS NULL

the query returns 

1   ABBOT.LE00  ABBOT.LE00  betty <br>
2   ACKERBRO00

ABBOT.LE00 shows up here because although he is in 'flutes', he is also in 'betty' which fills the != 'flutes' part of the condition.

Can anyone suggest a way to write the condition so that the query picks up everyone who is not in flutes, whether they are in another group or not.

Upvotes: 0

Views: 29

Answers (1)

Barmar
Barmar

Reputation: 782693

Put the condition in the ON clause.

select  a.PersonId, b.PersEmId, b.PersEmGR
from PersonRepDb a 
left join PersEm b on a.PersonId = b.PersEmId AND b.PersEmGR = 'flutes'
WHERE b.PersEmId IS NULL

Upvotes: 1

Related Questions