Query Help

[Muhd]

To start off i have a database table that consists of "profiles", another
that consists of "users" and finally one that consists of "exclusions ",
these are defined in the DDL below. I am trying to select all the profiles
minus any exclusions that are set up for that user but i keep getting
duplicate entries for profiles that aren't excluded and single entries for
profiles that are excluded.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'Users') DROP TABLE Users
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'User_Profile_E xclusions') DROP TABLE User_Profile_Ex clusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'Profiles') DROP TABLE Profiles


CREATE TABLE Users
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL
)

CREATE TABLE User_Profile_Ex clusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE Profiles
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Name VARCHAR(50) NOT NULL
)

INSERT INTO Users (Name) VALUES ('Bob')

INSERT INTO Profiles (Name) VALUES ('Microsoft')
INSERT INTO Profiles (Name) VALUES ('Intel')
INSERT INTO Profiles (Name) VALUES ('IBM')
INSERT INTO Profiles (Name) VALUES ('Sony')

INSERT INTO User_Profile_Ex clusions (User_ID,Profil e_ID) VALUES (1,3)
INSERT INTO User_Profile_Ex clusions (User_ID,Profil e_ID) VALUES (1,4)

SELECT P.*
FROM Profiles as P
JOIN User_Profile_Ex clusions AS UPE ON UPE.Profile_ID <> P.ID
WHERE UPE.User_ID = 1

The results i'm expecting to get back should look something like this:

ID Name
-----------------
1 Microsoft
2 Intel

Instead i'm getting back:

ID Name
-----------------
1 Microsoft
2 Intel
4 Sony
1 Microsoft
2 Intel
3 IBM

[David Portas]

Re: Query Help

SELECT P.id, P.name
FROM Profiles AS P
WHERE NOT EXISTS
(SELECT *
FROM User_Profile_Ex clusions
WHERE profile_id = P.id AND user_id = 1)

If you redesigned your schema replacing the Exclusions table with an
Inclusions table then this query would become a straightforward INNER JOIN
between the Profiles and UserProfiles. Recording inclusions seems like a
more natural way to do this than recording the exclusions but I suppose it
depends on your requirements.

--
David Portas
------------
Please reply only to the newsgroup
--

[Muhd]

Re: Query Help

I agree, missing from my example though is also an inclusion table and an
owners table. Basically i select all the profiles for a user that belong to
a specific owner minus exclusions plus any inclusions of profiles that
belong to a different owner. Boy thats a mouth full, basically a user can
be given access to all of a set of profiles minus exclusion OR they can be
given access to none of a set of profiles plus inclusions.

Thanks for your help, i just couldn't wrap my head around this yesterday but
your example is perfect.

Best,
Muhd.


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:jpSdnSSfsI 5wQ43dRVn-jA@giganews.com ...[color=blue]
> SELECT P.id, P.name
> FROM Profiles AS P
> WHERE NOT EXISTS
> (SELECT *
> FROM User_Profile_Ex clusions
> WHERE profile_id = P.id AND user_id = 1)
>
> If you redesigned your schema replacing the Exclusions table with an
> Inclusions table then this query would become a straightforward INNER JOIN
> between the Profiles and UserProfiles. Recording inclusions seems like a
> more natural way to do this than recording the exclusions but I suppose it
> depends on your requirements.
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>[/color]

[Muhd]

Re: Query Help

Argh, I don't know why i just can't wrap my head around this. After i get
this sorted out I'm gonna have to find time to sit and practice building
tables and queries until i fully understand things. In the meantime I got
the exclusions to work, then added another layer of complexity which worked
fine then tried to add an inclusion list but all i ever get for results is
the one profile on the inclusion list. Below is the modified DDL and DML
query, note the query doesn't select profiles that are in the inclusion
list. Basically i want to select all the profiles that belong to a specific
owner if a user has access to that owners profiles minus any profiles on the
exclusion list. So that works, but the extra layer is that a user may also
have a few profiles that he has been given specific access to view in the
inclusion list.

The end result will be four scenarios:
1) User can see all profiles for a given owner. (Working)
2) User can see all profiles for a given owner minus profiles on the
exclusion list. (Working)
3) User can see no profiles for a given owner. (Working)
4) User can see no profiles for a given owner plus profiles on the inclusion
list. (Not Working)

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'TUsers') DROP TABLE TUsers
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_ Lists') DROP TABLE TUser_Profile_L ists
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_ Exclusions') DROP TABLE TUser_Profile_E xclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_ Inclusions') DROP TABLE TUser_Profile_I nclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE TABLE_NAME
= 'TProfiles') DROP TABLE TProfiles


CREATE TABLE TUsers
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL
)

CREATE TABLE TUser_Profile_L ists
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Owner_ID INT NOT NULL
)

CREATE TABLE TUser_Profile_E xclusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE TUser_Profile_I nclusions
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Profile_ID INT NOT NULL
)

CREATE TABLE TProfiles
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Owner_ID INT NOT NULL,
Name VARCHAR(50) NOT NULL
)

INSERT INTO TUsers (Name) VALUES ('Bob')

INSERT INTO TUser_Profile_L ists (User_ID,Owner_ ID) VALUES (1,1)

INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Microsoft',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Intel',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('IBM',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sony',1)
INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sun Microsystems',2 )

INSERT INTO TUser_Profile_E xclusions (User_ID,Profil e_ID) VALUES (1,3)
INSERT INTO TUser_Profile_E xclusions (User_ID,Profil e_ID) VALUES (1,4)

INSERT INTO TUser_Profile_I nclusions (User_ID,Profil e_ID) VALUES (1,2)

DECLARE @userID INT
SET @userID = 1

SELECT P.*, UPL.*
FROM TProfiles AS P
JOIN TUser_Profile_L ists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_E xclusions WHERE Profile_ID = P.ID AND User_ID
= @userID
)

[Muhd]

Re: Query Help

I think i solved by simply doing a UNION on anthoer query specific to
inclusions but i would still love to hear from someone else if they other
ideas as how to solve this.

DECLARE @userID INT
SET @userID = 1

SELECT P.*
FROM TProfiles AS P
JOIN TUser_Profile_L ists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_E xclusions WHERE Profile_ID = P.ID AND User_ID
= @userID
)

UNION

SELECT P.*
FROM TProfiles AS P
JOIN TUser_Profile_I nclusions AS UPI ON UPI.Profile_ID = P.ID
WHERE UPI.User_ID = @userID

ORDER BY P.Name ASC

Best,
Muhd.

"Muhd" <eat@joes.com > wrote in message
news:jMfQb.2485 89$ts4.150985@p d7tw3no...[color=blue]
> Argh, I don't know why i just can't wrap my head around this. After i get
> this sorted out I'm gonna have to find time to sit and practice building
> tables and queries until i fully understand things. In the meantime I got
> the exclusions to work, then added another layer of complexity which[/color]
worked[color=blue]
> fine then tried to add an inclusion list but all i ever get for results is
> the one profile on the inclusion list. Below is the modified DDL and DML
> query, note the query doesn't select profiles that are in the inclusion
> list. Basically i want to select all the profiles that belong to a[/color]
specific[color=blue]
> owner if a user has access to that owners profiles minus any profiles on[/color]
the[color=blue]
> exclusion list. So that works, but the extra layer is that a user may[/color]
also[color=blue]
> have a few profiles that he has been given specific access to view in the
> inclusion list.
>
> The end result will be four scenarios:
> 1) User can see all profiles for a given owner. (Working)
> 2) User can see all profiles for a given owner minus profiles on the
> exclusion list. (Working)
> 3) User can see no profiles for a given owner. (Working)
> 4) User can see no profiles for a given owner plus profiles on the[/color]
inclusion[color=blue]
> list. (Not Working)
>
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE[/color]
TABLE_NAME[color=blue]
> = 'TUsers') DROP TABLE TUsers
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE[/color]
TABLE_NAME[color=blue]
> = 'TUser_Profile_ Lists') DROP TABLE TUser_Profile_L ists
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE[/color]
TABLE_NAME[color=blue]
> = 'TUser_Profile_ Exclusions') DROP TABLE TUser_Profile_E xclusions
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE[/color]
TABLE_NAME[color=blue]
> = 'TUser_Profile_ Inclusions') DROP TABLE TUser_Profile_I nclusions
> IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCH EMA.TABLES WHERE[/color]
TABLE_NAME[color=blue]
> = 'TProfiles') DROP TABLE TProfiles
>
>
> CREATE TABLE TUsers
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> NAME VARCHAR(50) NOT NULL
> )
>
> CREATE TABLE TUser_Profile_L ists
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Owner_ID INT NOT NULL
> )
>
> CREATE TABLE TUser_Profile_E xclusions
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL
> )
>
> CREATE TABLE TUser_Profile_I nclusions
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL
> )
>
> CREATE TABLE TProfiles
> (
> ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> Owner_ID INT NOT NULL,
> Name VARCHAR(50) NOT NULL
> )
>
> INSERT INTO TUsers (Name) VALUES ('Bob')
>
> INSERT INTO TUser_Profile_L ists (User_ID,Owner_ ID) VALUES (1,1)
>
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Microsoft',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Intel',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('IBM',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sony',1)
> INSERT INTO TProfiles (Name,Owner_ID) VALUES ('Sun Microsystems',2 )
>
> INSERT INTO TUser_Profile_E xclusions (User_ID,Profil e_ID) VALUES (1,3)
> INSERT INTO TUser_Profile_E xclusions (User_ID,Profil e_ID) VALUES (1,4)
>
> INSERT INTO TUser_Profile_I nclusions (User_ID,Profil e_ID) VALUES (1,2)
>
> DECLARE @userID INT
> SET @userID = 1
>
> SELECT P.*, UPL.*
> FROM TProfiles AS P
> JOIN TUser_Profile_L ists AS UPL ON UPL.Owner_ID = P.Owner_ID AND[/color]
UPL.User_ID[color=blue]
> = @userID
> WHERE NOT EXISTS
> (
> SELECT * FROM TUser_Profile_E xclusions WHERE Profile_ID = P.ID AND[/color]
User_ID[color=blue]
> = @userID
> )
>
>[/color]

[Erland Sommarskog]

Re: Query Help

Muhd (eat@joes.com) writes:[color=blue]
> CREATE TABLE TUsers
> ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> NAME VARCHAR(50) NOT NULL)
>
> CREATE TABLE TUser_Profile_L ists
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Owner_ID INT NOT NULL)
>
> CREATE TABLE TUser_Profile_E xclusions
> ( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL)
>
> CREATE TABLE TUser_Profile_I nclusions
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> User_ID INT NOT NULL,
> Profile_ID INT NOT NULL)
>
> CREATE TABLE TProfiles
> (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> Owner_ID INT NOT NULL,
> Name VARCHAR(50) NOT NULL)[/color]

I think you should review this schema. There are far too many identity
columns here, and I cannot really see any connection with the other
columns. Is TUsers.ID the same as User_ID in other tables? In such case
you should up a foreign-key constraint to establish this fact.

The ID column be warranted for in TUsers, but in TUser_Profile_L ists,
TUser_Profile_E xclusions and TUser_Profile_I nclusions, I suspect that
(User_ID, Owner_ID) and (User_ID, Profile_ID) respectively are meant to
be unique, and these pairs of columns should be the primary key for these
tables.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp