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 5 3905
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
--
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******** ************@gi ganews.com... 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 --
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
)
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" <ea*@joes.com > wrote in message
news:jMfQb.2485 89$ts4.150985@p d7tw3no... 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 (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_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)
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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:
|
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.
The web page on which the data is displayed has a list of countries.
Each country has a label heading under which websites are listed that
have information on that country. Any given website may appear many
times (under different countries) if it...
|
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 an offical 'date' is
proving tricky.
the last query that I ran, that I'm pretty sure should be working looks
like this:
(stdtime is the field with the date in numeric format, normally outpus
as: Tue Jan 6 11:36:24 2004)
select * from "table"...
|
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 sequential
number. My problem is, the make table query is taking all the
TransactionID's and putting them in the new table.
Is there a way to take the last transactionID only and put it in the new
table? So this way, when I clear the old Transactions,...
|
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 those 2
fields are null prior to the beginning date of my parameter.
The reason for this (to help make this a little clearer)is to pull
production into a query if it is a 'new' for the month. That is, never
run before the dates entered. I have 2...
| |
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 wouldn't work (nothing changed):
UPDATE tblManuals SET tblManuals.PARTNUM = Trim();
Would someone please tell me how to do an update query that will trim the
spaces?
|
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; however, the ATTRIB_DEF column
is important one. Currently the query gets all individuals that have
an ATTRIB_DEF that contains the string 'AC1' at the beginning of the
entry. I want the query to do the opposite. I want all of the
individuals...
|
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 been absent for over 8 days + in a 12 month period.
the relationship between tables is staffID. My sub table stores the absent occurences against my main Staff Names table. Obviously staff can have one or more occurences of being absent.
In my...
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |