AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS" (1 Viewer)

DevTycoon

Registered User.
Local time
Today, 04:06
Joined
Jun 14, 2014
Messages
94
Hello,

RE AdventureWorks2012

Any advice on how to pass a list of sub category id's to an "IN" statement, with the caviat that the list may be null depending on the user selection from an MS Access list box.

Source where I did the most homework on this:
http://www.sommarskog.se/index.html

-->http://www.sommarskog.se/arrays-in-sql.html
-->http://www.sommarskog.se/arrays-in-sql-2008.html


*FYI the WHERE clause is broken currently
Code:
use [AdventureWorks2012]
go

/*-------HOUSE KEEPING--------*/
drop table #SubCategoryUserSelection
go
drop table #CategoryUserSelection
go

/*---THIS WILL SIMULATE A USER SELECTION THAT FILLS A TEMP TABLE WITH ADODB CONNECTION----*/
select * into #SubCategoryUserSelection from
(
select distinct 
ProductSubcategoryID 
from Production.ProductSubcategory p
where ProductSubcategoryID in (1,2,3)
) x
go
select * into #CategoryUserSelection from
(
select distinct 
ProductCategoryID 
from Production.ProductSubcategory p
where ProductCategoryID in (1,2)
) x
go


/*-----RESULT WITH FILTER APPLIED-------*/
select 
 p.Name
,c1.ProductCategoryID
,c1.Name
,c1.ProductSubcategoryID 
,c2.Name
--,p.*
from Production.Product p
left join Production.ProductSubcategory c1 on  p.ProductSubcategoryID = c1.ProductSubcategoryID
left join Production.ProductCategory c2 on c1.ProductCategoryID = c2.ProductCategoryID
where   (case when exists(select 1 from #SubCategoryUserSelection x  where x.ProductSubcategoryID=c1.ProductSubcategoryID) then (select * from #SubCategoryUserSelection x  where x.ProductSubcategoryID=c1.ProductSubcategoryID) else 0 end  )
--		and
--		(exists(select 1 from #CategoryUserSelection x2  where x2.ProductCategoryID = c1.ProductCategoryID))
 

nfk

Registered User.
Local time
Today, 04:06
Joined
Sep 11, 2014
Messages
118
What your asking is incredibly unclear. I can foresee understanding the purpose of life prior to beginning to grasp the possibility of comprehending a minimum amount of this thread.
Nevertheless I'll take a shot because I'm feeling generous.

Your query makes no sense, why would you query for Ids that you are already passing as parameters?

Look:
Code:
select distinct 
	[B][COLOR="Red"]ProductSubcategoryID[/COLOR][/B] 
from Production.ProductSubcategory p
where [B][COLOR="red"]ProductSubcategoryID[/COLOR][/B] in (1,2,3)

:confused:

You can do:

Code:
SELECT
	p.Name,
	ps.ProductCategoryID,
	ps.Name,
	ps.ProductSubCategoryID,
	pc.Name
FROM
	Production.Product p
INNER JOIN Production.ProductSubcategory ps on p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc on p.ProductCategoryID = pc.ProductCategoryID
WHERE
	ps.ProductCategoryID IN (1,2,3)
	AND
	pc.ProductSubcategoryID IN (1,2,3)

Or as dynamic:

Code:
DECLARE @CatId VARCHAR(20) = '1,2,3'
DECLARE @SubCatId VARCHAR(20) = '1,2,3'
DECLARE @Sql VARCHAR(MAX) =
'SELECT
	p.Name,
	ps.ProductCategoryID,
	ps.Name,
	ps.ProductSubCategoryID,
	pc.Name
FROM
	Production.Product p
INNER JOIN Production.ProductSubcategory ps on p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc on p.ProductCategoryID = pc.ProductCategoryID
WHERE
	ps.ProductCategoryID IN ('+@CatId+')
	AND
	pc.ProductSubcategoryID IN ('+@SubCatId+')'

EXEC(@Sql)

Or with a query inside the IN:

Code:
SELECT
	p.Name,
	ps.ProductCategoryID,
	ps.Name,
	ps.ProductSubCategoryID,
	pc.Name
FROM
	Production.Product p
INNER JOIN Production.ProductSubcategory ps on p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc on p.ProductCategoryID = pc.ProductCategoryID
WHERE
	ps.ProductCategoryID IN (
		SELECT ProductCategoryID 
		FROM Production.ProductCategory
		WHERE ProductCagetory.Name LIKE 'C%' -- Categories beggining with 'C'
	)
	AND
	pc.ProductSubcategoryID IN (
		SELECT ProductSubcategoryID 
		FROM Production.ProductSubcategory
		WHERE ProductSubcategory.Name LIKE '%a' -- Subcategories ending with 'A'
	)

or with ProductSubcategory based on product category id:

Code:
SELECT
	p.Name,
	ps.ProductCategoryID,
	ps.Name,
	ps.ProductSubCategoryID,
	pc.Name
FROM
	Production.Product p
INNER JOIN Production.ProductSubcategory ps on p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc on p.ProductCategoryID = pc.ProductCategoryID
WHERE
	pc.ProductSubcategoryID IN (
		SELECT ProductSubcategoryID 
		FROM Production.ProductSubcategory
		WHERE ProductCategory.ProductCategoryID IN (1,2,3)
	)
 

DevTycoon

Registered User.
Local time
Today, 04:06
Joined
Jun 14, 2014
Messages
94
I can foresee understanding the purpose of life prior to beginning to grasp the possibility of comprehending a minimum amount of this thread.

Hello,

My original problem is that if a user has no filter applied the WHERE clause does not return any records when using IN.
 

nfk

Registered User.
Local time
Today, 04:06
Joined
Sep 11, 2014
Messages
118
Hello,

My original problem is that if a user has no filter applied the WHERE clause does not return any records when using IN.

Yes, that's exactly right. That's how the IN condition works.

So what? you want a default value in case the user doesn't select anything?, then do that on the frontend, make the selection/input control have a default value.
 

DevTycoon

Registered User.
Local time
Today, 04:06
Joined
Jun 14, 2014
Messages
94
Yes, that's exactly right. That's how the IN condition works.

So what? you want a default value in case the user doesn't select anything?

I need to be able to return all results if there is no filter passed by the user.
 

kevlray

Registered User.
Local time
Today, 04:06
Joined
Apr 5, 2010
Messages
1,046
In MS-SQL you would have a case statement to check for a NULL.
 

Minty

AWF VIP
Local time
Today, 11:06
Joined
Jul 26, 2013
Messages
10,354
Simply add a If condition block to not add the Where statement in your SQL procedure.

Code:
IF MySearchCondion IS NULL 
BEGIN
       SELECT blah blah 
END
ELSE
BEGIN
       SELECT Blah Blah WHERE MySearchCondition
END
 

Users who are viewing this thread

Top Bottom