asked on

Conditional Left Join

Is there a way to conditionally do a left join?

Basic layout of the problem is that I'm joining to a contacts table. I want to grab contact type X, but if it doesn't exist, then I want to grab contact type Y.

LEFT JOIN [tbl_sb_contactsToProjects] [cont]
ON [proj].[ID] = [cont].[projectID] AND ([cont].[roleid] = 11)
If this is null, then I want to pull from ([cont].[roleid] = 10)

Please keep in mind that this is in the middle of a query that is joining 5 or 6 tables already, so it won't be easy to completely change things.

Thanks for the help!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GENTP

ASKER

Thanks guys, once I looked up how to use a coalesce, it made perfect sense.

Final solution is something along the lines of (abreviated):

SELECT [addr].[CompanyName] as [Builder]
FROM [tbl_sb_projects] as [proj]
LEFT JOIN [tbl_sb_contactsToProjects] [cont]
      ON [proj].[ID] = [cont].[projectID] AND ([cont].[roleid] = 11)
LEFT JOIN [tbl_sb_contactsToProjects] [cont2]
      ON [proj].[ID] = [cont2].[projectID] AND ([cont2].[roleid] = 10)
LEFT JOIN [tbl_companyAddress] [addr]
      ON COALESCE([cont].[contactID], [cont2].[contactID]) = [addr].[CompanyID]