multiple select box query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

multiple select box query

Author

Topic

mby_shaman
Starting Member

2 Posts

Posted - 2002-11-22 : 12:38:38

I have 5 select boxes that I want an intersection from. Currently my sql does a union on the items using OR in my query:

SELECT items
FROM tblOrderDetail od INNER JOIN
tblOrderHeader oh ON od.intOrderID = oh.intOrderID
WHERE (oh.strLocationID = @sLocID)
OR (oh.strTandemShipToID = @sShipTo)
OR (od.strMfgCode = @sMfgCode)
OR (od.strAddUserID = @sCSR)
OR (oh.intOrderHeaderStatus = @iStatus)

How can I setup my query so that if a user chooses 2 dropdown boxes the items become an intersected set?

Thanks,
-- Shaman
======================================================
There are 10 kinds of people in the world. Those
that know binary, and those that don't

Edited by - mby_shaman on 11/22/2002 12:39:39

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-22 : 13:14:15

Dynamic where clause
http://www.sqlteam.com/item.asp?ItemID=2077

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 13:44:36

I think you want to use AND's -- but only for the criteria the users actually pick, if i am reading you right.

SELECT items
FROM tblOrderDetail od
INNER JOIN
tblOrderHeader oh
ON od.intOrderID = oh.intOrderID
WHERE (oh.strLocationID = ISNULL(@sLocID,oh.strLocationID))
AND (oh.strTandemShipToID = ISNULL(@sShipTo,oh.strTandemShipToID))
AND (od.strMfgCode = ISNULL(@sMfgCode,strMfgCode))
AND (od.strAddUserID = ISNULL(@sCSR,strAddUserID))
AND (oh.intOrderHeaderStatus = ISNULL(@iStatus,intOrderHeaderStatus))

Check Books On Line if you are not familiar with the ISNULL funciton; it's very handy. This is assuming parameters you are not usingin your criteria are NULL.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 14:42:53

By the way, Shaman -- I like your quote ! very clever.

- Jeff

mby_shaman
Starting Member

2 Posts

Posted - 2002-11-22 : 20:04:30

Thanks guys. I was just running thru my SQL books and realized that COALESCE would be the best method for me to use. Then I got the email ;)

Neat tip:
Build dropdowns by setting up multiple queries that include your <option> tags. Using an ADODB Recodset, Do a rs.GetString() to output your tags. Then use NextRecodset to get the next list of options. Just did that one today ;)

-- Shaman

;) Suman
---------------------------------
There are 10 kinds of people in the world. Those that understand binary, and those that don't.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-23 : 06:31:25

http://www.thinkgeek.com/tshirts/frustrations/5aa9

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources