Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-27-2017, 09:53 AM   #1
DevTycoon
Newly Registered User
 
Join Date: Jun 2014
Location: New Mexico
Posts: 94
Thanks: 42
Thanked 3 Times in 3 Posts
DevTycoon is on a distinguished road
Smile AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

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))

DevTycoon is offline   Reply With Quote
Old 09-05-2017, 07:10 AM   #2
nfk
Newly Registered User
 
Join Date: Sep 2014
Location: All the way south.
Posts: 117
Thanks: 15
Thanked 5 Times in 5 Posts
nfk is an unknown quantity at this point
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

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 
	ProductSubcategoryID 
from Production.ProductSubcategory p
where ProductSubcategoryID in (1,2,3)


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)
	)
__________________
Microsoft Access Hater...

Proud owner of 2 bans.
nfk is offline   Reply With Quote
The Following User Says Thank You to nfk For This Useful Post:
DevTycoon (09-10-2017)
Old 09-10-2017, 10:52 AM   #3
DevTycoon
Newly Registered User
 
Join Date: Jun 2014
Location: New Mexico
Posts: 94
Thanks: 42
Thanked 3 Times in 3 Posts
DevTycoon is on a distinguished road
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

Quote:
Originally Posted by nfk View Post
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.

DevTycoon is offline   Reply With Quote
Old 09-11-2017, 03:19 AM   #4
nfk
Newly Registered User
 
Join Date: Sep 2014
Location: All the way south.
Posts: 117
Thanks: 15
Thanked 5 Times in 5 Posts
nfk is an unknown quantity at this point
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

Quote:
Originally Posted by DevTycoon View Post
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.
__________________
Microsoft Access Hater...

Proud owner of 2 bans.
nfk is offline   Reply With Quote
Old 09-11-2017, 05:50 AM   #5
DevTycoon
Newly Registered User
 
Join Date: Jun 2014
Location: New Mexico
Posts: 94
Thanks: 42
Thanked 3 Times in 3 Posts
DevTycoon is on a distinguished road
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

Quote:
Originally Posted by nfk View Post
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.
DevTycoon is offline   Reply With Quote
Old 09-11-2017, 06:16 AM   #6
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 585
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

In MS-SQL you would have a case statement to check for a NULL.
kevlray is offline   Reply With Quote
Old 09-11-2017, 06:35 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,435
Thanks: 128
Thanked 1,475 Times in 1,447 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: AdventureWorks 2012 t-sql where clause using "IN" / "EXISTS"

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


__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
null params , t-sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Input " Like A* " in Validation Rule Property but it shows " ALike "A*" " AccessPractice Tables 2 06-24-2016 05:37 AM
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Field Properties (Date) auto "/" & "2012" amerifax General 6 09-13-2012 07:28 AM
"Invalid Function Argument" in EXISTS clause + crash platedslicer Queries 7 08-10-2012 05:31 AM
query criteria - building "In" clause in "hidden" textbox - mult values wware Forms 3 03-11-2007 06:25 AM




All times are GMT -8. The time now is 05:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World