473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Query Help

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_SCHEMA.TABLES WHERE TABLE_NAME
= 'Users') DROP TABLE Users
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'User_Profile_Exclusions') DROP TABLE User_Profile_Exclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.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_Exclusions
(
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_Exclusions (User_ID,Profile_ID) VALUES (1,3)
INSERT INTO User_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)

SELECT P.*
FROM Profiles as P
JOIN User_Profile_Exclusions 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
Jul 20 '05 #1
5 3865
SELECT P.id, P.name
FROM Profiles AS P
WHERE NOT EXISTS
(SELECT *
FROM User_Profile_Exclusions
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
--
Jul 20 '05 #2
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" <RE****************************@acm.org> wrote in message
news:jp********************@giganews.com...
SELECT P.id, P.name
FROM Profiles AS P
WHERE NOT EXISTS
(SELECT *
FROM User_Profile_Exclusions
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
--

Jul 20 '05 #3
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_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUsers') DROP TABLE TUsers
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Lists') DROP TABLE TUser_Profile_Lists
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Exclusions') DROP TABLE TUser_Profile_Exclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
= 'TUser_Profile_Inclusions') DROP TABLE TUser_Profile_Inclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.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_Lists
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Owner_ID INT NOT NULL
)

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

CREATE TABLE TUser_Profile_Inclusions
(
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_Lists (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_Exclusions (User_ID,Profile_ID) VALUES (1,3)
INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)

INSERT INTO TUser_Profile_Inclusions (User_ID,Profile_ID) VALUES (1,2)

DECLARE @userID INT
SET @userID = 1

SELECT P.*, UPL.*
FROM TProfiles AS P
JOIN TUser_Profile_Lists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_Exclusions WHERE Profile_ID = P.ID AND User_ID
= @userID
)
Jul 20 '05 #4
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_Lists AS UPL ON UPL.Owner_ID = P.Owner_ID AND UPL.User_ID
= @userID
WHERE NOT EXISTS
(
SELECT * FROM TUser_Profile_Exclusions WHERE Profile_ID = P.ID AND User_ID
= @userID
)

UNION

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

ORDER BY P.Name ASC

Best,
Muhd.

"Muhd" <ea*@joes.com> wrote in message
news:jMfQb.248589$ts4.150985@pd7tw3no...
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_SCHEMA.TABLES WHERE TABLE_NAME = 'TUsers') DROP TABLE TUsers
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TUser_Profile_Lists') DROP TABLE TUser_Profile_Lists
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TUser_Profile_Exclusions') DROP TABLE TUser_Profile_Exclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TUser_Profile_Inclusions') DROP TABLE TUser_Profile_Inclusions
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.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_Lists
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
User_ID INT NOT NULL,
Owner_ID INT NOT NULL
)

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

CREATE TABLE TUser_Profile_Inclusions
(
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_Lists (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_Exclusions (User_ID,Profile_ID) VALUES (1,3)
INSERT INTO TUser_Profile_Exclusions (User_ID,Profile_ID) VALUES (1,4)

INSERT INTO TUser_Profile_Inclusions (User_ID,Profile_ID) VALUES (1,2)

DECLARE @userID INT
SET @userID = 1

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

Jul 20 '05 #5
Muhd (ea*@joes.com) writes:
CREATE TABLE TUsers
( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL)

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

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

CREATE TABLE TUser_Profile_Inclusions
(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)


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_Lists,
TUser_Profile_Exclusions and TUser_Profile_Inclusions, 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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
14
by: Bruce W...1 | last post by:
I do a query to MySQL using PHP. Well actually I do too many of them which is the problem, it's too slow. I think maybe an inner join or something would be better but I can't figure this out. ...
1
by: Hought, Todd | last post by:
Hi all, trying to run a query against a table, to pull the date out, and order it. problem is, the date is stored in character (string) format, not as an actual timestamp, so parsing it back into...
1
by: PMB | last post by:
Thank you in advance for any and all assistance. I'm trying to use a make table query to pull the last transactionID, so I can use an append query to reset the transactionID to the next...
5
by: Norma | last post by:
I am trying to make a query pull data from between the dates I enter in the parameter but also look back 'in time' to see where 2 other fields have null values, and only pull data into the query if...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
2
by: schoultzy | last post by:
Hello Everyone, This is probably a simple fix so be kind when/if you reply. The query below retrieves information for individuals based on a column named ATTRIB_DEF, and assorted other columns;...
6
by: leeg | last post by:
Help please. Apologies but I am poor in access and programming :o) and am having trouble getting my head around this one!!...again!!! I need to have a query or report to flag up someone who has...
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.