Disregard null parameter in WHERE clause

I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.See if this article on Dynamic Search Conditions by Erland helps:
http://www.sommarskog.se/dyn-search.html
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.|||DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main
LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
AND (Sub.sub_key_col = @.sub_key_col OR @.sub_key_col IS NULL)
WHERE Main.main_key_col = @.main_key_col
Jacco Schalkwijk
SQL Server MVP
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
>I have a problem optionally using a parameter to query a second key
> column in an outer joined table:
> DROP TABLE Sub;
> DROP TABLE Main;
> CREATE TABLE Main (
> main_key_col INTEGER NOT NULL PRIMARY KEY,
> main_data_col VARCHAR(15) NOT NULL
> )'
> CREATE TABLE Sub (
> main_key_col INTEGER NOT NULL
> REFERENCES Main (main_key_col),
> sub_key_col INTEGER NOT NULL,
> PRIMARY KEY (main_key_col, sub_key_col),
> sub_data_col VARCHAR(15) NOT NULL
> );
> INSERT INTO Main VALUES (1,'Ford Model T');
> INSERT INTO Main VALUES (2,'Ferrari GTB');
> INSERT INTO Sub VALUES (2,1,'Red');
> INSERT INTO Sub VALUES (2,2,'Yellow');
> INSERT INTO Sub VALUES (2,3,'Silver');
> To return the full 'denormalized' set:
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
> Now I want to use two parameters for the respective key columns with
> the sub_key_col parameter 'optional', meaning if it's NULL it is not
> used in the WHERE clause. In the past I've got away with a trick like:
> Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
> but, because of the outer join, sub_key_col can be null and NULL = NULL
> removes the row, of course.
> I have a solution but it isn't very satisfactory:
> DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
> SET @.main_key_col = 2
> SET @.sub_key_col = NULL
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
> WHERE Main.main_key_col = @.main_key_col
> AND
> CASE WHEN @.sub_key_col IS NULL THEN 1
> WHEN Sub.sub_key_col = @.sub_key_col THEN 1
> ELSE 0 END = 1
> Is there a better way?
> Thank you.
>|||COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
Sub.sub_key_col, '?')|||--CELKO-- wrote:
> COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
> Sub.sub_key_col, '?')
Now this I like because I get to use COALESCE after all! I'm now off to
order 'SQL Programming Style' <g>
Thanks everyone.