conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )

|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...
Im just hoping I've explained myself clearly now :)
Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!

Conditional Join

I want to join 2 tables conditionally. One order needs to join with one
instruction. The three potential join fields are: Country, Exchange, and
Type. These fields are required in the Orders table but only Country is
required in Instructions. The data entry requirements of the application are
such that if an instruction has a Type it must have an Exchange.
The logic of the join is that:
1-if all three fields match, Country, Exchange, and Type then join those
records.
2-if two fields match, Country, and Exchange then join those records.
3-if one field matches, Country, then join those records.
My expected results given the sample data is as follows.
SELECT OrderID,InstructionID FROM Orders
JOIN ...
--Expected Results
OrderID,InstructionID
1,1
2,5
3,5
4,7
5,8
6,9
7,13
CREATE TABLE Orders
(
OrderID int NOT NULL,
Country char (3)NOT NULL,
Exchange char (3)NOT NULL,
Type char (3)NOT NULL,
)
CREATE TABLE Instructions
(
InstructionID int NOT NULL,
Country char (3) NOT NULL,
Exchange char (3) NULL,
Type char (3) NULL,
Instructions varchar (15)NOT NULL
)
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(1,'USA','NYS','Buy','Instruction 1')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(2,'USA','NYS','Sel','Instruction 2')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(3,'USA','NYS',NULL,'Instruction 3')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(4,'USA',NULL,NULL,'Instruction 4')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(5,'CAN','TSE',NULL,'Instruction 5')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(6,'CAN','ALB',NULL,'Instruction 6')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(7,'ESP',NULL,NULL,'Instruction 7')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(8,'ESP','MAD',NULL,'Instruction 8')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(9,'IRQ','BAG','Buy','Instruction 9')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(10,'IRQ','BAG','Sel','Instruction 10 ')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(11,'DUE',NULL,NULL,'Instruction 11')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(12,'DUE','HAM',NULL,'Instruction 12')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(13,'DUE','HAM','Buy','Instruction 13')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(14,'DUE','HAM','Sel','Instruction 14')Terri (terri@.cybernets.com) writes:
> I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
Thanks a lot for table and test data. This may not be the smartest
query, but it's easy to understand:
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange )
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange
AND O.Type = I2.Type)
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
AND O.Type = I.Type
ORDER BY OrderID
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I want to join 2 tables conditionally. <<
That makes NO sense as you have to join or not join a table.
Okay.
Gee, too bad that SQL and RDBMS has columns and not fields; rows are
not records. They are nothing alike in concept or execution. Also,
"type" is too vague to be a valid column name - "type" of
what? This is basics, damn it!!
xchange.
The logic of the join is that:
1-if all three fields [sic] match, Country, Exchange, and Type [sic]
then join those records [sic].
2-if two fields [sic] match, Country, and Exchange then join those
records [sic]. <
3-if one field [sic] matches, Country, then join those records [sic].
<<
Okay, try this:
SELECT ..
FROM Foo, Bar, etc.
WHERE CASE WHEN Foo.country_code= Bar.country_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.exchange_code = Bar.exchange_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.vague_type =Bar.vague_type
THEN 1 ELSE 0 END > 1 ;|||Terri
CREATE VIEW myView
AS
SELECT
OrderID, InstructionID,
I.Country ,I.Exchange ,I.Type FROM Orders O
JOIN Instructions I ON O.Country=COALESCE(I.Country,O.Country)
AND O.Exchange=COALESCE(I.Exchange,O.Exchange) AND
O.Type=COALESCE(I.Type,O.Type)
WHERE I.Exchange IS NOT NULL
--Final Select
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE Type IS NOT NULL
UNION
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE (SELECT COUNT(*) AS dp
FROM myView V WHERE OrderID=myView.OrderID ) =1
"Terri" <terri@.cybernets.com> wrote in message
news:dr3mjc$r6g$1@.reader2.nmix.net...
>I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are
> such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
> SELECT OrderID,InstructionID FROM Orders
> JOIN ...
>
> --Expected Results
> OrderID,InstructionID
> 1,1
> 2,5
> 3,5
> 4,7
> 5,8
> 6,9
> 7,13
> CREATE TABLE Orders
> (
> OrderID int NOT NULL,
> Country char (3)NOT NULL,
> Exchange char (3)NOT NULL,
> Type char (3)NOT NULL,
> )
> CREATE TABLE Instructions
> (
> InstructionID int NOT NULL,
> Country char (3) NOT NULL,
> Exchange char (3) NULL,
> Type char (3) NULL,
> Instructions varchar (15)NOT NULL
> )
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (1,'USA','NYS','Buy','Instruction 1')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (2,'USA','NYS','Sel','Instruction 2')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (3,'USA','NYS',NULL,'Instruction 3')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (4,'USA',NULL,NULL,'Instruction 4')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (5,'CAN','TSE',NULL,'Instruction 5')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (6,'CAN','ALB',NULL,'Instruction 6')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (7,'ESP',NULL,NULL,'Instruction 7')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (8,'ESP','MAD',NULL,'Instruction 8')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (9,'IRQ','BAG','Buy','Instruction 9')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (10,'IRQ','BAG','Sel','Instruction 10 ')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (11,'DUE',NULL,NULL,'Instruction 11')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (12,'DUE','HAM',NULL,'Instruction 12')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (13,'DUE','HAM','Buy','Instruction 13')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (14,'DUE','HAM','Sel','Instruction 14')
>
>