T-SQL - How to write a conditional join
join, t-sql
Solution
If I understand correctly it sounds like your join conditions would be the equivalent of `ON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID))` and likewise for the group join.
I use this conditional join at times.
Problem
I have a stored procedure with a number of parameters. I would like to write my query so that it joins with certain tables but only if a particular parameter has a value. Take the following example: I have a Person table. There is also an Address table which holds Person Addresses and a Groups table that holds Person Groups. Both are one to many relationships with the Person table. My stored procedure has an @AddressID parameter and a @GroupID parameter. The query always just returns fields from the Person table. If neither parameter has a value then the query should return all records from the Person table. If the @AddressID parameter is supplied then it should return only records that have a matching record in the Address table and ignore the Groups table. If the @GroupID parameter is supplied then it should return only records that have a matching record in the Groups table and ignore the Addresses table. If both parameters are supplied then it should only show records that have a matching record in both tables. Make sense? Is there a simple way to do this that I am missing? Thanks, Corey