CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
Xoduszero
Xoduszero

Reputation: 15

SQL Left Join Conditions

Hello Everyone and Anyone,

I have an issue I was hoping to resolve using a Left Join using conditions, however, I have yet to figure it out and I'm beginning to get a headache. I am looking to pull data in using a left join however I only want two columns to be included and I want the condition to be based on a 3rd column from this second table. Also I am open to solutions that don't necessarily require a left join condition.

Select a.ID,
a.AssociateName,
a.FormTypeID
b.Segment1
b.Segment2
From NinjaTable a
Left Join ShinobiTable b On b.AssociateName = a.AssociateName

Here is the condition that I need (I am going to word it in an if statement although i know it does not apply) What makes this tricky is it is possible for the associate to have more than 1 assignment Status. They may have moved to another department and their previous listing now shows inactive however they still have another active one. I need the condition to essentially grab the Active one only if applicable else if there is not an Active one then grab the inactive one

IF b.AssignmentStatus = 'Active' then perform left join Normally
IF b.AssignmentStatus = 'Inactive' Then only perform left join if there is not an active one.

Upvotes: 1

Views: 148

Answers (1)

gordy
gordy

Reputation: 9786

You can include both status records and then coalesce them, e.g:

Select a.ID,
a.AssociateName,
a.FormTypeID,
coalesce(b.Segment1, c.Segment1),
coalesce(b.Segment2, c.Segment2)
From NinjaTable a
Left Join ShinobiTable b On b.AssociateName = a.AssociateName and b.AssignmentStatus = 'Active'
Left Join ShinobiTable c On c.AssociateName = a.AssociateName and c.AssignmentStatus = 'Inactive'

Upvotes: 1

Related Questions