Narrowed Down Search

[Looch]

Hi All,

I'm trying to write a query that gets more granular as criteria is
available. for example:

select name where A is true, B MIGHT be true and C MIGHT be true.

Is that possible? The idea is that the more information available the
smaller the size of the dataset.

Thanks for any input.

[Erland Sommarskog]

Looch (lu**********@yahoo.com) writes:

I'm trying to write a query that gets more granular as criteria is
available. for example:

select name where A is true, B MIGHT be true and C MIGHT be true.

Is that possible? The idea is that the more information available the
smaller the size of the dataset.

I'm not sure that I understand. SQL Server evaluates the conditions in
the WHERE clause as it sees fit. If the conditions are simple column
conditions, SQL Server is likely to evaluate them at once for the same
row. If the conditions refers to other table, there are more variations.

Maybe you could post a clarifying example by posting CREATE TABLE statements
for your tables and some INSERT statements with sample data and what result
you desire from the sample?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Chris.Cheney]

Looch <lu**********@yahoo.comwrote in news:1178739215.805604.182750
@y5g2000hsa.googlegroups.com:

Hi All,

I'm trying to write a query that gets more granular as criteria is
available. for example:

select name where A is true, B MIGHT be true and C MIGHT be true.

Is that possible? The idea is that the more information available the
smaller the size of the dataset.

Thanks for any input.

Perhaps something like

SELECT name FROM atable WHERE ColA = ValueA AND ColB = COALESCE(@B, ColB)
AND ColC = COALESCE(@C, ColC)

[Looch]

Erland,

I'll explain a little more. I'm just looking for a select query result
to fill a dataset.

I have a three text boxes in a VB app that I want to use as criteria
for a query.

txt1 = Serial Number
txt2 = Ship Date
txt3 = Item Description

All three item attributes (txt1 - 3) are columns in table that
includes much more information (Customer Name, Address, etc). Granted
this isn't ideally following the relationtional model.

If the user knows the Serial Number, that will return one record which
would be great. If they know only the Item Description that may return
100 records. If they know the Item Description and Ship Date that may
narrow it down to 25 records. I don't want to require txt1 - 3 be
included in the query but if the information is available than it
would be used in the query to help narrow down the search.

Thanks again.

[Ed Murphy]

Looch wrote:

I have a three text boxes in a VB app that I want to use as criteria
for a query.

txt1 = Serial Number
txt2 = Ship Date
txt3 = Item Description

All three item attributes (txt1 - 3) are columns in table that
includes much more information (Customer Name, Address, etc). Granted
this isn't ideally following the relationtional model.

If the user knows the Serial Number, that will return one record which
would be great. If they know only the Item Description that may return
100 records. If they know the Item Description and Ship Date that may
narrow it down to 25 records. I don't want to require txt1 - 3 be
included in the query but if the information is available than it
would be used in the query to help narrow down the search.

select <columns>
from <table>
where (@sn is null or sn = @sn)
and (@sd is null or sd = @sd)
and (@desc is null or desc = @desc)

[Erland Sommarskog]

Looch (lu**********@yahoo.com) writes:

I'll explain a little more. I'm just looking for a select query result
to fill a dataset.

I have a three text boxes in a VB app that I want to use as criteria
for a query.

txt1 = Serial Number
txt2 = Ship Date
txt3 = Item Description

All three item attributes (txt1 - 3) are columns in table that
includes much more information (Customer Name, Address, etc). Granted
this isn't ideally following the relationtional model.

If the user knows the Serial Number, that will return one record which
would be great. If they know only the Item Description that may return
100 records. If they know the Item Description and Ship Date that may
narrow it down to 25 records. I don't want to require txt1 - 3 be
included in the query but if the information is available than it
would be used in the query to help narrow down the search.

While the query suggested by Ed works, I would recommend this:

IF @serialnumber IS NOT NULL
BEGIN
SELECT ...
FROM tbl
WHERE serialnumber = @serialnumber
END
ELSE IF @shipdate IS NOT NULL
BEGIN
SELECT ...
FROM tbl
WHERE shipdate = @shipdate
AND itemdescription LIKE coalesce(@itemdesc, '%')
END
ELSE
BEGIN
SELECT ...
FROM tbl
WHERE itemdescription LIKE @itemdesc
END

The reason for this is performance. Assuming that the table has some
size, users don't want to wait a long time if they enter a serial
number. I assume that there is a unique index on the serial number.
I also assume that there is an index on shipdate, which is why I
single out this criteria as well. If there is no index on shipdate,
there is no point in this.

You may think: what if there are even more choices, isn't this getting
very complex then? Yes, and in that case dynamic SQL is to prefer. But
with only three choices, the above works well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx