November 18, 2012 at 7:57 pm
Hey all,
Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables:
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)
)
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:
SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ONTableA_ID = A.ID
AND TableB_ID = ISNULL(@TableB_ID, TableB_ID)
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:
SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ONTableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE TableB_ID END) = TableB_ID
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:
DECLARE @TableB_ID INT
IF @TableB_ID IS NULL
SELECT
ID
FROM TableA
ELSE
SELECT
A.ID
FROM TableA A
JOIN LinkTable ONTableA_ID = A.ID
AND TableB_ID = @TableB_ID
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?
November 19, 2012 at 6:03 am
Hm. On thinking about it a bit more, I guess I could use a subquery:
SELECT
A.ID
FROM TableA A
WHERE
(
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
Also very ugly though...
And one other solution I've come up with:
SELECT
A.ID
FROM TableA A
LEFT JOIN LinkTable ONTableA_ID = A.ID
WHERE (CASE WHEN @TableB_ID IS NOT NULL THEN @TableB_ID ELSE ISNULL(TableB_ID, -1) END) = ISNULL(TableB_ID, -1)
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.
November 19, 2012 at 10:25 am
Before using one or another technique, you should read this article from one of the best experts in this area:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
November 19, 2012 at 10:51 am
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.
November 19, 2012 at 10:57 am
kramaswamy (11/19/2012)
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.
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:
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
November 19, 2012 at 11:06 am
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?
November 19, 2012 at 11:20 am
kramaswamy (11/19/2012)
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?
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:
select *
from (values (1),(2),(3)) c(c)
where not (c = null)
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 NULL
November 20, 2012 at 6:07 pm
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.
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA
IF OBJECT_ID('tempdb..#LinkTable') IS NOT NULL
DROP TABLE #LinkTable
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
DROP TABLE #TableB
CREATE 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 #TableA
SELECT 1,'Apples'
UNION
SELECT 2,'Pears'
UNION
SELECT 3,'Oranges'
UNION
SELECT 4,'Grapes'
INSERT INTO #TableB
SELECT 1,'Oranges'
UNION
SELECT 2,'Onions'
UNION
SELECT 3,'Apples'
UNION
SELECT 4,'Squash'
INSERT INTO #LinkTable
SELECT 1,3
UNION
SELECT 3,1
DECLARE @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 a
LEFT OUTER JOIN
#LinkTable AS lt
ON a.ID = lt.TableA_ID
LEFT OUTER JOIN
#TableB AS b
ON lt.TableB_ID = b.ID
WHERE
--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)))
November 21, 2012 at 5:43 am
Steven Willis (11/20/2012)
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.
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA
IF OBJECT_ID('tempdb..#LinkTable') IS NOT NULL
DROP TABLE #LinkTable
IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
DROP TABLE #TableB
CREATE 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 #TableA
SELECT 1,'Apples'
UNION
SELECT 2,'Pears'
UNION
SELECT 3,'Oranges'
UNION
SELECT 4,'Grapes'
INSERT INTO #TableB
SELECT 1,'Oranges'
UNION
SELECT 2,'Onions'
UNION
SELECT 3,'Apples'
UNION
SELECT 4,'Squash'
INSERT INTO #LinkTable
SELECT 1,3
UNION
SELECT 3,1
DECLARE @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 a
LEFT OUTER JOIN
#LinkTable AS lt
ON a.ID = lt.TableA_ID
LEFT OUTER JOIN
#TableB AS b
ON lt.TableB_ID = b.ID
WHERE
--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)))
Why would you go with such over-kill? OP doesn't need LinkTable, His TableB has FK directly to TableA.
November 23, 2012 at 6:07 pm
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.
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply