Select a subset of a table based on condition

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

Select a subset of a table based on condition

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-08-28 : 13:41:28

Hi,

This is what I am trying to do:

A.
Rows for User 1. The number of items a user 1 (loginID =1) has access to:
select * from UserAppAccess where LoginId = 1

B:
This is the list of rows for User 2 (number of items user 2 access to)
select * from UserAppAccess where LoginId = 2

I need to get the list of A and B, substract A from B.

The the combined unique keys are "ApplicationCode" and "ConstrainID" in this table. So, I need to get rows from A where the combination of ApplicationCode and ConstrainID do no exist in 2.

How do I go about it? Do I have to concatenate these 2 columns to get this? Do I have to load A and B in temp tables before I do the compare?

Thanks for you time..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 13:53:12

You can use either query below - the second works only on SQL 2005 or later:

SELECT
	b.*
FROM
	UserAppAccess b
WHERE
	NOT EXISTS
	(
		SELECT * FROM UserAppAccess a
		WHERE b.ApplicationCode = a.ApplicationCode
		AND a.ConstraintId = b.ConstraintId
		AND a.loginId = 1
	)
	AND b.loginId = 2;

---
SELECT
	ApplicationCode,ConstraintId
FROM
	UserAppAccess
WHERE
	loginId = 2
EXCEPT
SELECT
	ApplicationCode,ConstraintId
FROM
	UserAppAccess
WHERE
	loginId = 1

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-08-28 : 15:16:48

Super. Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources