Return Result not working with IN Clause

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

General SQL Server Forums

New to SQL Server Programming

Return Result not working with IN Clause

Author

Topic

uholbrx
Starting Member

4 Posts

Posted - 2008-02-28 : 12:06:22

I have a form that has many checkboxes (more than 40) that provide information about companies.

For example. Company ABC
Checkbox 1 (Windows XP)
Checkbox 2 (Windows Vista)
Checkbox 3 (Windows NT4)
etc.

I save these in a SQL table like the following
Row 1 - Col 1 (CompanyID), col2 (checkbox value)
Row 2 - Col 1 (CompanyID), col2 (checkbox value)
etc.. so a comapany can have multiple checkbox selected..

I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.

This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.

I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx)
INTERSECT
(SELECT CompanyID from xx WHERE CheckboxVal = xx)
etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.

Any suggestions on this please...this is my 4th day working on this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 12:18:04

[code]SELECT t.CompanyID
FROM
(
SELECT CompanyID from Table WHERE CheckboxVal = xx
UNION ALL
SELECT CompanyID from Table WHERE CheckboxVal = yy
)t
GROUP BY t.CompanyID
HAVING COUNT(*) =2[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-28 : 12:43:29

Visakh's method is probably the fastest. But, if you need a method to handle a variable number of check boxes here is a sample (note you would need to populate the "ToFind" table in some way, possible a separated list that you would need to split or something)

DECLARE @CompanyOS TABLE (CompanyID INT, CheckID INT)

INSERT @CompanyOS
SELECT 1, 1
UNION ALL SELECT 1, 2
UNION ALL SELECT 1, 3
UNION ALL SELECT 2, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 3

DECLARE @ToFind TABLE (CheckID INT)

INSERT @ToFind
SELECT 2
UNION ALL SELECT 3

-- Dynamic method
SELECT
	C.CompanyID
FROM
	@CompanyOS AS C
INNER JOIN
	@ToFind AS T
	ON C.CheckID = T.CheckID
GROUP BY
	C.CompanyID
HAVING
	COUNT(*) = (SELECT COUNT(*) FROM @ToFind)

uholbrx
Starting Member

4 Posts

Posted - 2008-02-28 : 12:55:04

I not sure this approach would work - I have tried a similar approach using the following:

BEGIN
CREATE TABLE #MyTempTable3 (inTEFirmID int PRIMARY KEY)
INSERT #MyTempTable3 EXEC(@vChkBoxes)
END

Where @vCheckboxes was a srting I parsed within ASP.NET code. A sample is like:
'SELECT nCompanyID FROM Company WHERE (inChkVal = 1)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 3)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 5)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 10)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 15)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 22)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 54)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 55)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 56)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 58)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 59)
INTERSECT
SELECT nCompanyID FROM Company WHERE (inChkVal = 63)'

I run perfect when the query is only about 5-10 options selected - however the potentail exist the end user could select many checkboxes - which could be over 50... I get the following message when this happens..

"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

I have reported this to our DBA - however they said this issues was fixed via a SP2 (SQL 2005 BTW) update and that the single statement im trying to execute is too large to parse... But im not sure of any other route to accomplish this..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-28 : 13:16:34

If you are building a dynamic sql query in ASP you could modify the sample I provided to be soemthing like:

SELECT
	C.nCompanyID
FROM
	@Company AS C
INNER JOIN
	(
		SELECT 1 AS inChkVal
		UNION ALL SELECT 2
		UNION ALL SELECT 10
		UNION ALL SELECT 59
		-- ETC..
	) AS T
	ON C.inChkVal = T.inChkVal
GROUP BY
	C.nCompanyID
HAVING
	COUNT(*) = -- Whatever the count should be.

uholbrx
Starting Member

4 Posts

Posted - 2008-02-28 : 14:36:24

WOW! - I just implemented this code into my .net code - and WOW does it run fast. I am still trying to piece together and fully understand it myself - but MANY THANKS for this and you've saved my sanity - at least for now..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:02:30

Or something like this

SELECT		nCompanyID
FROM		Company
WHERE		inChkVal IN (1, 3, 5, 10, 15, 22, 54, 55, 56, 58, 59, 63)
GROUP BY	Company
HAVING		COUNT(DISTINCT inChkVal) = 12

E 12°55'05.25"
N 56°04'39.16"

uholbrx
Starting Member

4 Posts

Posted - 2008-02-28 : 16:35:39

This just keeps getting better and better... This is even easier than before - Thanks Again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:47:50

I missed an "n" character in the GROUP BY clause.