October 29, 2009 at 7:36 am
I have three tables:
Table User:
ID
Name
...
Table Doc:
ID
Name
Group
...
Table UserDoc:
ID
FkUserId
FkDocId
(I know that the table design is not nice but it's not my choice)
What I want to do is the following:
I want to get the users that have ALL the documents listed to a certain group.
Why? I want to add a new document with a certain group and want all the users that have access to all the document of a certain group to also have access to this document. This how far I have got with SQL code and I think it should be possible to optimize the sqlstatement even further...
SELECT InnerSearch.Na, COUNT (*) AS Number FROM (SELECT Doc.Grupp AS Document ,User.NAME AS Na FROM User join UserDoc ON UserDoc.FkUserId=User.ID JOIN Doc ON Doc.ID=UserDoc.FkDocId WHERE Doc.Group='Grouptocheckfor' ) AS InnerSearch GROUP BY InnerSearch.Na
How should this SQL statement look like? I still get back to much information that is I even get back the users that have not all documents in group and should be a check to see if the maximum number of documents to certain group is compared to the result from what we get from current statement.
October 29, 2009 at 9:07 am
Its not tuned for performance, but this is what I got as a first thought. Please test extensively if it works okay for you. Let us also wait to see if someone has a better query too. Thanks.
Create table #user(id int, name varchar(50))
Create table #tdoc(id int, name varchar(50), dgroup varchar(50))
Create table #Udoc(id int, FKUID int, FKDID int)
delete from #user
INSERT INTO #USER values (1, 'Tom')
INSERT INTO #USER values (2, 'tim')
INSERT INTO #USER values (3, 'Har')
delete from #tdoc
Select * from #tdoc
INSERT INTO #tdoc VAlues( 1, 'book1', 'Group1')
INSERT INTO #tdoc VAlues( 2, 'book2', 'Group1')
INSERT INTO #tdoc VAlues( 3, 'book3', 'Group1')
INSERT INTO #tdoc Values (4, 'Book1', 'group2')
INSERT INTO #Udoc VALUES(1, 1, 1)
INSERT INTO #Udoc VALUES(2, 1, 2)
INSERT INTO #Udoc VALUES(3, 1, 3)
INSERT INTO #Udoc VALUES(4, 2, 1)
INSERT INTO #Udoc VALUES(5, 2, 2)
INSERT INTO #Udoc VALUES(6, 3, 1)
INSERT INTO #Udoc VALUES(7, 1, 4)
INSERT INTO #Udoc VALUES(8, 2, 3)
Select FKUID from
(Select FKUID, FKDID from #udoc U
JOIN #tdoc T ON U.FKDID = T.ID
Where T.dgroup = 'Group1'
Group by FKUID, FKDID) tbl
Group by FKUID
Having count (*) = (select count(*)
from #tdoc t
Where T.dGroup = 'Group1'
Group by dGroup )
---------------------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply