Conditional Select - 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 2005 Forums

Transact-SQL (2005)

Conditional Select

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-20 : 10:37:52

Hello All,

I have a cursor which retrieves the items based by zip codes based only those where the user has access to see them.

There are two tables UserAccess and UserZips involved to check the user access to area.

If user has access to certain zips then those will be listed in the UserZips table and if user has access to all the zips then there will not be a record in UserZips instead there is a flag in UserAccess table AllZips which would be = 1.

Here is the query to get the zips for entered user.


SELECT b.ZipCodes * FROM UserAccess a
INNER JOIN UserZips b
	ON a.UserID = b.UserID
WHERE a.UserID = 8970

I am stuck at the second case where if the user has access to all the zips then I want -1 to return. I have to use zips values to process orders further.

I would appreciate any help.

Thanks,
-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-20 : 11:49:02

Can any expert help me with this problem?

Thanks in advance,
-S

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-20 : 12:38:05

If a User has the access to all zips then you want that shown select to return -1 ???

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-20 : 12:38:55

Here is ~~two~~ a way which I think should work for you:


IF EXISTS (SELECT * FROM UserAccess WHERE UserID = 8970 AND AllZips = 1)
BEGIN
	SELECT -1 AS ZipCodes
END
ELSE
BEGIN
	SELECT 
		b.ZipCodes 
	FROM 
		UserAccess a
	INNER JOIN 
		UserZips b
		ON a.UserID = b.UserID
	WHERE 
		a.UserID = 8970
END

EDIT: Missed the part about not having and records in UserZips..

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-20 : 12:42:01

Actually no. If the user has access to all the zips then there should not be anything in where clause against the zipID so that means all the zips. And if user has access to limited zips then list them as IN or EXISTS clause in where from UserZips.ZipCodes.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-20 : 12:46:57

If you needed it without the IF you could use a UNION:

SELECT 
	b.ZipCodes 
FROM 
	UserAccess a
INNER JOIN 
	UserZips b
	ON a.UserID = b.UserID
WHERE 
	a.UserID = 8970
	AND a.AllZips = 0 -- Assunming Zero
	
UNION ALL

SELECT -1 AS ZipCodes
FROM UserAccess 
WHERE UserID = 8970 AND AllZips = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources