January 30, 2019 at 11:57 am
I have a table that, when @networkID has a value, I need to join into a query and limit the results. What I mean is, I don't need the joined table limiting the results unless the variable has a value. The only way I can think of is to left join, make a temp table, then add an if/then statement to select out differently based on if the variable is null.
I have greatly simplified my code below. Technically, I could just have two queries, but I hate diverging code for individual use cases because maintenance becomes a pain. If there is a way to limit results only when @networkID has a value without temp tables I would love to know. This comes up for me often.
Thank you for your help!
SELECT
*
FROM
[ewylPacks] ep
left join [ewylPacksXModules] pm on ep.PackID = pm.PackID
left join [ewylModules] em on pm.ModuleID = em.ModuleID
left join [ewylLessons] el on em.ModuleID = el.ModuleID
left join [ewylLessonsXResources] lXr on el.LessonID = lXr.LessonID
Left join [ewylResources] er on lXr.ResourceID = er.ResourceID
/* THIS IS THE TABLE I NEED TO LIMIT RESULTS - If @networkID has a value then it needs to only return those that match.
If @networkID is null, it needs to return all results like this table was never joined (I left it as a left join for now - which wouldn't limit results) */
left join [ewylResourcesXNetwork] erxn on erxn.resourceID = er.ResourceID and erxn.networkID = isNull(@networkID,erxn.networkID)
January 30, 2019 at 2:53 pm
brandon 94807 - Wednesday, January 30, 2019 11:57 AMI have a table that, when @networkID has a value, I need to join into a query and limit the results. What I mean is, I don't need the joined table limiting the results unless the variable has a value. The only way I can think of is to left join, make a temp table, then add an if/then statement to select out differently based on if the variable is null.I have greatly simplified my code below. Technically, I could just have two queries, but I hate diverging code for individual use cases because maintenance becomes a pain. If there is a way to limit results only when @networkID has a value without temp tables I would love to know. This comes up for me often.
Thank you for your help!
SELECT
*
FROM
[ewylPacks] ep
left join [ewylPacksXModules] pm on ep.PackID = pm.PackID
left join [ewylModules] em on pm.ModuleID = em.ModuleID
left join [ewylLessons] el on em.ModuleID = el.ModuleID
left join [ewylLessonsXResources] lXr on el.LessonID = lXr.LessonID
Left join [ewylResources] er on lXr.ResourceID = er.ResourceID/* THIS IS THE TABLE I NEED TO LIMIT RESULTS - If @networkID has a value then it needs to only return those that match.
If @networkID is null, it needs to return all results like this table was never joined (I left it as a left join for now - which wouldn't limit results) */
left join [ewylResourcesXNetwork] erxn on erxn.resourceID = er.ResourceID and erxn.networkID = isNull(@networkID,erxn.networkID)
Superficially, I use
tablecolumn = @parameter or @parameter is null
as the join predicate. That way if @parameter is not null then it gets used, and if @parameter is null then the predicate
@parameter is null
returns true all the time.
Theres a complication to this however which most folks here will give you a heads up on. If @parameter is not null the first time you run your query, possibly you'll get a query plan that works best when @parameter is not null but works poorly if @parameter is null or vice versa.
I don't know if it still applies when you are only having one set of these, but the pattern seems to indicate the possibility, if you run into poor performance when @parameter is null but good performance when it isn't or the other way around, that'd be the first thing I'd suggest. In fact, read the included link anyways for general information about this sort of thing.
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/ has the updated scoop on this phenom.
Apologies if I've misunderstood or the advice doesn't apply!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply