Conditional Join

Question

Conditional Join

cdun2

SSCertifiable

Points: 7893
More actions
October 13, 2006 at 2:12 pm

#115885

Hello,
I need to include a JOIN on the condition of the value of variable named @AcctCodeValue. If the value of @AcctCodeValue <> 'All' then I don't need the join. Otherwise, I do.
Seems like an IF statement would do the trick, maybe I've just implemented it incorrectly. The JOIN in a FALSE result would look like this;
INNER JOIN #acctCodeMc ac ON ac.value = fm.fm_cust_account_cd
How would I go about this? If you need more information, please let me know.
Thank you for your help!
CSDunn

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Without the rest of your query or more explanation of what you are trying to provide, it is a little difficult to help. But let me provide a few thoughts that may help.

Yes, you could use two different queries with an IF...ELSE statement. It would also help to make the code in your sproc more readable and understandable at a later time or by someone else. The down side is that it will likely not be compiled or cached, as the execution plan will differ on each query (testing and reviewing execution plan will help you determine this).

From the fragment you provide, it appears that you may be only performing the join to validate or limit the results to a account code, and are not retreiving any data values from the joined table fm_cust_account_cd. If so, you may want to consider doing just a correlated subquery as part of the where clause. Something like the following:

SELECT ac.*
FROM #acctCodeMc ac
WHERE 
  (ac.Value in (SELECT fm_cust_account_cd from fm) OR
   @AcctCodeValue = 'All')

I'm not sure if there are more criteria for fm table. But perhaps this may help you trigger an alternate plan that may work better for you.

Hope this helps

Mark

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

more detail;

I have a Proc that contains a parameter called @AcctCodeValue varchar(1000). The user can provide one value or a comma delimited string of values to this parameter. One of those parameter values can be 'All'.

If NOT @AcctCodeValue = 'All', then a temporary table is populated with the comma delimited string values by using a UDF called fn_split. This function parses the comma delimited string, and creates a record for every string value, and returns a table. The condition looks like this;

IF

NOT @AcctCodeValue = 'All'

BEGIN

INSERT #acctCodesMc

(idx, value)

SELECT

idx, ltrim(value)

FROM

dbo.fn_split(@AcctCodeValue,',')

END

If I have data in this temp table, then I need to use the following JOIN;

INNER JOIN #acctCodesMc ac ON ac.Value = fm.fm_cust_account_cd

If @AcctCodeValue = 'All' the temp table will not have any data in it, and basically, the JOIN would need to look like this;

RIGHT JOIN #acctCodesMc ac ON ac.Value = fm.fm_cust_account_cd

So if @AcctCodeValue <> 'All', I need the fm.fm_cust_account_cd values that match what the temp table has. Otherwise, I need 'All' fm.fm_cust_account_cd values.

Does this help? Thank you again for your assistance.

CSDunn

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 3

It's probably worth mentioning that you can select from and join to a table-value function. If I understand what you're asking would something like this work?

SELECT @AcctCodeValue = Replace(@AcctCodeValue,' ', '')

SELECT fm.blah
FROM FM
    LEFT OUTER JOIN dbo.fn_split(@AcctCodeValue,',') ac
        ON Fm.fm_cust_account_cd = ac.Value
WHERE (ac.idx IS NOT NULL OR @AcctCodeValue = 'All')

It's worth mentioning that I've seen the (expression or TRUE) approach sometimes increase the cost of a query.

Let me know if this helps.

Thanks!

SQL guy and Houston Magician

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

CSDunn; I beleive you describe a good case for use the correlated sub-query instead of the join, as I described in my previous post. I think you will see that it will work well in your scenario.

Robert: that issue you describe is exactly why a correlated subquery works better than a join, in this situation. The query optimizer will make a slightly different plan if it knows that sometimes there are all records or no records based on the WHERE clause.

Hope this helps

Mark

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

Thank you for your help. I will pursue the correlated sub-query idea. If I run into problems with it, I'll pursue 'plan b', and may post again.

CSDunn

Vladan SSC-Insane Points: 22072 More actions · Answer 6

You can find some good ideas regarding usage of parameter lists (including the possibility to search for all if parameter is not supplied) here:

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/arrays-in-sql.html

cdun2 SSCertifiable Points: 7893 More actions · Answer 7

Thanks again, I really appreciate the assistance!

CSDunn