Find Who is NOT granted
-
i've got two tables, one a list of users, and another is a list of documents, and who has been "granted" access to document. I need the reverse of the information...for record1, who has not been "granted"
and i can't seem to get my arms around the sql;
here's sample data:
--###################################
create table #granted(grantedid int,grantedname varchar(30) )
create table #temp(recid int ,recdescrip varchar(30) ,grantedid int)
insert into #granted(grantedid,grantedname) values (1,'Lowell')
insert into #granted(grantedid,grantedname) values (2,'Remi')
insert into #granted(grantedid,grantedname) values (3,'Frank')
insert into #granted(grantedid,grantedname) values (4,'Noel')
insert into #temp(recid,recdescrip,grantedid) values(1,'rec1',1)
insert into #temp(recid,recdescrip,grantedid) values(1,'rec1',2)
insert into #temp(recid,recdescrip,grantedid) values(2,'rec2',2)
insert into #temp(recid,recdescrip,grantedid) values(3,'rec3',2)
insert into #temp(recid,recdescrip,grantedid) values(3,'rec3',3)
insert into #temp(recid,recdescrip,grantedid) values(4,'rec4',4)
--this shows who is explicitly granted
select #temp.recid,
#temp.recdescrip,
#temp.grantedid,
#granted.grantedname
from #temp
inner join #granted on #temp.grantedid=#granted.grantedid
order by recid,grantedname
--how do i get the list that shows that Frank has NOT been granted access to records 1,2 and 4?
--actually i want a list that shows for each document, who does not have access from #granted
i can join tables on data where the elements have a relationship, but how do i show a "missing" relationship?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You're missing a table in this design, should be :
Users
Documents
PermissionsUsersDocuments
It takes all three of these tables to create this query.
-
the documents table exists, i guess i just didn't include in in the example for brevity;
that may give me the kick in the pants i needed; i didn't think about additionally joining on the third table.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Maybe you should have used more xml to solve this problem
. -
I tried the alternate method, but the excess violence just caused me to replace the keyboard
still trying to figure out; this sql gives all the possible combinations, but i still cannot figure out how to rip out the items that exist in the #temp table
the missing join condition does cross join? i think?
select distinct #temp.recid,
#temp.recdescrip,
#temp.grantedid,
#granted.grantedname
from #temp,#granted
order by recid,grantedname
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I either don't understand your design or there's a missing link.
You need all 3 tables for this, the temp table is useless in this case to get that info (unless you might wanna use not in/exists), but you still need the base tables to create this one
-
Yes you need the cross join to get all the posible combinations and then left join to the the granted (third table that Remi is metioning)
Something like:
Select All.DocID, All.UserID
from (select DocID, UserID
from Docs cross join Users) All
Left join Grants G on All.DocID = G.DocID and All.UserID = G.UserID
where G.UserID IS NULL
* Noel -
Yup, that good old cartesian result
. -
Tricky little problem. There were some interesting twists with the cross join. Here's my whack at it (Although you've probably got it by now already):
select x.recid, x.recdescrip, x.grantedid as NotGrantedToId, x.grantedname as NotGrantedToName
from
(
select distinct b.recid, b.recdescrip, c.grantedid, c.grantedname
from
#temp b
cross join #granted c
) x
left join #temp d
on x.recid = d.recid
and x.grantedid = d.grantedid
where d.recid is null
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply