InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 03:33:11 GMT20http://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspx[quote]Eugene Elutin (11/21/2012)Why would you go with such over-kill? OP doesn't need LinkTable, His TableB has FK directly to TableA.[/quote]First of all, in the OP the example is using a "link" table. So in my example I used one too. If you will notice in my sample data, the keys in the first table neither match those in the second table nor are all of the keys even in the "link" table. Since a "link" table WAS described in the OP I assumed that such relationships (or lack thereof) would have to be related by the cross-reference or there wouldn't be a need for the "link" table (as you pointed out). Whether or not this is a good schema design is beside the point. If Table1 and Table2 did have a FK relationship then sure the middle join could be left out and the where clause simplified. If what I posted is useful to anyone (especially Kramaswamy) then I met my objective. If I've misunderstood or misinterpreted his requirements based on insufficient data (or maybe even because I'm just tired and cranky at times) and the code I posted has no value in this specific case...well, then someone can provide a better solution and I will be pleased to add it to my T-SQL toolbox. Fri, 23 Nov 2012 18:07:40 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspx[quote][b]Steven Willis (11/20/2012)[/b][hr]I think this may do what you are trying to do. If the search term is in both tables then the matching row only from Table A displays, otherwise ALL the rows in Table A are displayed.[code="sql"]IF OBJECT_ID('tempdb..#TableA') IS NOT NULLDROP TABLE #TableAIF OBJECT_ID('tempdb..#LinkTable') IS NOT NULLDROP TABLE #LinkTableIF OBJECT_ID('tempdb..#TableB') IS NOT NULLDROP TABLE #TableBCREATE TABLE #TableA ( [ID] INT NOT NULL, [Val] VARCHAR(10) NULL, PRIMARY KEY (ID), UNIQUE (ID))CREATE TABLE #LinkTable( [ID] INT IDENTITY(1,1) NOT NULL, [TableA_ID] INT NULL, [TableB_ID] INT NULL, PRIMARY KEY (ID), UNIQUE (ID))CREATE TABLE #TableB ( [ID] INT NOT NULL, [Val] VARCHAR(10) NULL, PRIMARY KEY (ID), UNIQUE (ID)) INSERT INTO #TableASELECT 1,'Apples'UNIONSELECT 2,'Pears'UNIONSELECT 3,'Oranges'UNIONSELECT 4,'Grapes'INSERT INTO #TableBSELECT 1,'Oranges'UNIONSELECT 2,'Onions'UNIONSELECT 3,'Apples'UNIONSELECT 4,'Squash'INSERT INTO #LinkTableSELECT 1,3UNIONSELECT 3,1DECLARE @strSearch VARCHAR(50)SET @strSearch = 'Apples'--SET @strSearch = 'Pears'--SET @strSearch = 'Oranges'--SET @strSearch = 'Onions'--SET @strSearch = ''SELECT a.ID ,a.Val FROM #TableA AS aLEFT OUTER JOIN #LinkTable AS lt ON a.ID = lt.TableA_IDLEFT OUTER JOIN #TableB AS b ON lt.TableB_ID = b.IDWHERE --if @strSearch in A and in B then matching row from A ((a.Val = ISNULL(NULLIF(@strSearch,''),0) AND (b.Val = ISNULL(NULLIF(@strSearch,''),0))) --if @strSearch not A or not B then ALL rows from A OR ((NOT EXISTS (SELECT ID FROM #TableA WHERE val = @strSearch) OR NOT EXISTS (SELECT ID FROM #TableB WHERE val = @strSearch)) AND EXISTS (SELECT ID FROM #TableA)))[/code][/quote]Why would you go with such over-kill? OP doesn't need LinkTable, His TableB has FK directly to TableA.Wed, 21 Nov 2012 05:43:40 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxI think this may do what you are trying to do. If the search term is in both tables then the matching row only from Table A displays, otherwise ALL the rows in Table A are displayed.[code="sql"]IF OBJECT_ID('tempdb..#TableA') IS NOT NULLDROP TABLE #TableAIF OBJECT_ID('tempdb..#LinkTable') IS NOT NULLDROP TABLE #LinkTableIF OBJECT_ID('tempdb..#TableB') IS NOT NULLDROP TABLE #TableBCREATE TABLE #TableA ( [ID] INT NOT NULL, [Val] VARCHAR(10) NULL, PRIMARY KEY (ID), UNIQUE (ID))CREATE TABLE #LinkTable( [ID] INT IDENTITY(1,1) NOT NULL, [TableA_ID] INT NULL, [TableB_ID] INT NULL, PRIMARY KEY (ID), UNIQUE (ID))CREATE TABLE #TableB ( [ID] INT NOT NULL, [Val] VARCHAR(10) NULL, PRIMARY KEY (ID), UNIQUE (ID)) INSERT INTO #TableASELECT 1,'Apples'UNIONSELECT 2,'Pears'UNIONSELECT 3,'Oranges'UNIONSELECT 4,'Grapes'INSERT INTO #TableBSELECT 1,'Oranges'UNIONSELECT 2,'Onions'UNIONSELECT 3,'Apples'UNIONSELECT 4,'Squash'INSERT INTO #LinkTableSELECT 1,3UNIONSELECT 3,1DECLARE @strSearch VARCHAR(50)SET @strSearch = 'Apples'--SET @strSearch = 'Pears'--SET @strSearch = 'Oranges'--SET @strSearch = 'Onions'--SET @strSearch = ''SELECT a.ID ,a.Val FROM #TableA AS aLEFT OUTER JOIN #LinkTable AS lt ON a.ID = lt.TableA_IDLEFT OUTER JOIN #TableB AS b ON lt.TableB_ID = b.IDWHERE --if @strSearch in A and in B then matching row from A ((a.Val = ISNULL(NULLIF(@strSearch,''),0) AND (b.Val = ISNULL(NULLIF(@strSearch,''),0))) --if @strSearch not A or not B then ALL rows from A OR ((NOT EXISTS (SELECT ID FROM #TableA WHERE val = @strSearch) OR NOT EXISTS (SELECT ID FROM #TableB WHERE val = @strSearch)) AND EXISTS (SELECT ID FROM #TableA)))[/code]Tue, 20 Nov 2012 18:07:22 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspx[quote][b]kramaswamy (11/19/2012)[/b][hr]Hm - I'm actually kinda surprised that works. I would have thought it would fail because the WHERE condition would evaluate AS@TableB_ID IS NULL (TRUE) OR TableB_ID (Value OR NULL) = @TableB_ID (NULL)I would have thought that the second part of that would evaluate to undefined, since you are comparing a NULL value with an equals operator. Then, since that one is undefined, the whole condition would evaluate to undefined, since you're ORing TRUE with undefined.I guess that the second condition though, will instead evaluate as FALSE instead of undefined, and thus allow it to work?[/quote]No, that is not exactly right. The second condition is not evaluated as FALSE and it's easy to check, let see the following example:[code="sql"] select * from (values (1),(2),(3)) c(c) where not (c = null)[/code]if "c=null" evaluates as FALSE, then "not (c = null)" would evaluate to true and all records will be returned. But it doesn't happen as "c = null" evaluates to UNDEFINED, as you rightly thought in the first place!The important bit in the used where condition is "OR", since OR evaluates to TRUE if any of the logical parts evaluates to TRUE, it doesn't really care that "TableB_ID = @TableB_ID(NULL)" evaluates as UNDEFINED!So, used WHERE works simply like that:If @TableB_ID is not null, it will check if the value in TableB_ID from LinkedTable is equal to it, basically turning LEFT JOIN to INNER JOIN!If @TableB_ID is null, JOIN is not working at all as condition evaluates to UNDEFINED, but this is ignored completely as part of evaluating result of logical OR operator, which returns true for @TableB_ID IS NULLMon, 19 Nov 2012 11:20:12 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxHm - I'm actually kinda surprised that works. I would have thought it would fail because the WHERE condition would evaluate AS@TableB_ID IS NULL (TRUE) OR TableB_ID (Value OR NULL) = @TableB_ID (NULL)I would have thought that the second part of that would evaluate to undefined, since you are comparing a NULL value with an equals operator. Then, since that one is undefined, the whole condition would evaluate to undefined, since you're ORing TRUE with undefined.I guess that the second condition though, will instead evaluate as FALSE instead of undefined, and thus allow it to work?Mon, 19 Nov 2012 11:06:22 GMTkramaswamyhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspx[quote][b]kramaswamy (11/19/2012)[/b][hr]Yeah - I've used the Dynamic SQL approach before, and it does work pretty well, but I'm not a huge fan of it. Never knew about the RECOMPILE option though, I'll have to try that out.Still, doesn't really help with the question at hand. I mean, yes - I could use the dynamic SQL solution, and that would solve my problem. If there is no better, more elegant solution, then I suppose I'll just go with that.[/quote]As per article, Dynamic SQL, most likely, would give you the best performance, but if you insist, there is another way to write your query:[code="sql"] DECLARE @TableB_ID INT SELECT DISTINCT A.ID FROM TableA A LEFT JOIN LinkTable LT ON LT.TableA_ID = A.ID WHERE @TableB_ID IS NULL OR LT.TableB_ID = @TableB_ID [/code]Mon, 19 Nov 2012 10:57:24 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxYeah - I've used the Dynamic SQL approach before, and it does work pretty well, but I'm not a huge fan of it. Never knew about the RECOMPILE option though, I'll have to try that out.Still, doesn't really help with the question at hand. I mean, yes - I could use the dynamic SQL solution, and that would solve my problem. If there is no better, more elegant solution, then I suppose I'll just go with that.Mon, 19 Nov 2012 10:51:27 GMTkramaswamyhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxBefore using one or another technique, you should read this article from one of the best experts in this area: [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Mon, 19 Nov 2012 10:25:43 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxHm. On thinking about it a bit more, I guess I could use a subquery:[code]SELECT A.IDFROM TableA AWHERE ( CASE WHEN @TableB_ID IS NOT NULL THEN CASE WHEN EXISTS (SELECT 1 FROM LinkTable WHERE TableA_ID = A.ID AND TableB_ID = @TableB_ID) THEN 1 ELSE 0 END ELSE 1 END) = 1[/code]Also very ugly though...And one other solution I've come up with:[code]SELECT A.IDFROM TableA ALEFT JOIN LinkTable ON TableA_ID = A.IDWHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE ISNULL(TableB_ID, -1) END) = ISNULL(TableB_ID, -1)[/code]This one of course is reliant upon using a number for the ISNULL function which is guaranteed to never occur. Sure, it might work in some cases, but it still feels more like a hack than anything else.Mon, 19 Nov 2012 06:03:32 GMTkramaswamyhttp://www.sqlservercentral.com/Forums/Topic1386129-338-1.aspxHey all,Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables:[code]CREATE TABLE TableA( ID INT IDENTITY PRIMARY KEY, Val VARCHAR(10))CREATE TABLE LinkTable( ID INT IDENTITY PRIMARY KEY, TableA_ID INT, TableB_ID INT)CREATE TABLE TableB( ID INT IDENTITY PRIMARY KEY, Val VARCHAR(10))[/code]Now, suppose I want to have a stored procedure, in which I get back all of the records from TableA. The stored procedure should have an input parameter, which defaults to NULL, which will allow me to search for values from TableA which have a corresponding value linked to TableB.Normally, I would write the query in the form:[code]SELECT A.IDFROM TableA ALEFT JOIN LinkTable ON TableA_ID = A.ID AND TableB_ID = ISNULL(@TableB_ID, TableB_ID)[/code]The problem of course, is that this won't actually filter the result set, because I'm using a LEFT JOIN. Of course, if I were to change it to be a JOIN, then I would be forcing it to return only records from TableA which have a corresponding TableB value, which I also don't want.Normally, I'd solve this by using a format similar to the following:[code]SELECT A.IDFROM TableA ALEFT JOIN LinkTable ON TableA_ID = A.IDWHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE TableB_ID END) = TableB_ID[/code]The problem here is that if there is no record in LinkTable for TableB, then the WHERE clause would evaluate to (Value = NULL), which will always return false. Normally, I'd handle that by saying (Value IS NULL), but there is no way for me to modify my CASE statement to produce that kind of output.The only solution I've been able to come up with is the following:[code]DECLARE @TableB_ID INTIF @TableB_ID IS NULL SELECT ID FROM TableAELSE SELECT A.ID FROM TableA A JOIN LinkTable ON TableA_ID = A.ID AND TableB_ID = @TableB_ID[/code]But this solution is kinda ugly, since it forces me to use an IF-ELSE statement in a stored proc, or to just use two stored procs, neither of which I like.Does anyone have a better solution?Sun, 18 Nov 2012 19:57:46 GMTkramaswamy