Selecting "All" records where "All" is actually only partial records

Question

Selecting "All" records where "All" is actually only partial records

OceanDiscovery

Old Hand

Points: 355
More actions
June 27, 2010 at 3:57 pm

#220544

I have a list which displays filter options for the user, based on settings for that user.
So the table might contain the following records:
1Group 1
2Group 2
3Group 3
4Group 4
5Group 5
6Group 6
7Group 7
8Group 8
9Group 9
10Group 10
11Group 11
12Group 12
But for the current user, they may only have a list which shows:
0(All)
3Group 3
5Group 5
9Group 9
However, when I return records if the user selects (All); I return all records from the table which may (and most likely will) include records from other groups not applicable to this user.
I populate the list for the user to select from a stored procedure.
Is it possible to return "(All)" records for this user for only those Groups in their list; being 3, 5, or 9?
If the parameter value is 0, can I use the stored procedure for the list to create the filter values; or are there other options open to me.
Any help is greatly appreciated.
The current user interface is from an Access Project.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nugby SSCommitted Points: 1612 More actions · Answer 1

Would definitely help to post some sample SQL re the User table and the Group table but I'd assume you'd identify the Groups the User has access to either as a String or Array Variable and then use that in place of Zero in a Case When or IF statement as per the Query you want to use.

Quatrei.X SSCrazy Points: 2697 More actions · Answer 2

Hi there,

did you want something like this?

DECLARE @tbl table (id int,word nvarchar(max))

DECLARE @value nvarchar(max)

--sample table and data

insert into @tbl

values (1,'the'),(2,'big'),(3,'brown'),(4,'fox'),(5,'jumps'),(6,'over'),(7,'the'),(8,'lazy'),(9,'dog'),(10,'and...'),(11,'fin!')

set @value='2,10,5,4' -- filter parameters (try adding zero ex. '2,5,0,4')

set @value=@value+','

;with cte (id,value) as

(

select cast(left(@value,charindex(',',@value)-1) as int),right(@value,len(@value)-charindex(',',@value))

union all

select cast(left(value,charindex(',',value)-1) as int),right(value,len(value)-charindex(',',value))

from cte

where charindex(',',value)<>0

)

select * from @tbl o

where o.id in (select case

when i.id=0 then o.id

else i.id

end

from cte i)

Hope it helps ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

shield_21 Ten Centuries Points: 1033 More actions · Answer 3

Hi Mac,

I believe this has relation to your previous post.. 😀

I created table mapping between users and groups here, so it can retrieve only the groups where a particular user belongs..

DECLARE @Group TABLE (groupid int, groupname varchar(max))

DECLARE @User TABLE (userid int, username varchar(max))

DECLARE @User_Group TABLE (id int identity, userid int, groupid int)

--You can create foreign key to enforce referential integrity

INSERT INTO @Group

SELECT 1,'Group 1' UNION ALL

SELECT 2,'Group 2' UNION ALL

SELECT 3,'Group 3' UNION ALL

SELECT 4,'Group 4' UNION ALL

SELECT 5,'Group 5' UNION ALL

SELECT 6,'Group 6' UNION ALL

SELECT 7,'Group 7' UNION ALL

SELECT 8,'Group 8' UNION ALL

SELECT 9,'Group 9' UNION ALL

SELECT 10,'Group 10' UNION ALL

SELECT 11,'Group 11' UNION ALL

SELECT 12,'Group 12'

INSERT INTO @User

SELECT 1,'User 1' UNION ALL

SELECT 2,'User 2'

INSERT INTO @User_Group --Sample data only: user 1 belongs to groups 3, 5, 9; user 2 belongs to groups 1,2,3

SELECT 1,3 UNION ALL

SELECT 1,5 UNION ALL

SELECT 1,9 UNION ALL

SELECT 2,1 UNION ALL

SELECT 2,2 UNION ALL

SELECT 2,3

DECLARE @groupids VARCHAR(MAX)

SET @groupids = '0' --comma separated.. Replace this with desired values

DECLARE @userid INT

SET @userid = 1 --sample user

--generate xml structure

DECLARE @xml XML

SET @xml = CAST('<root>' + REPLACE(@groupids,',','</root><root>') + '</root>' AS XML)

SELECT g.groupid, g.groupname

FROM

(

SELECT groups.gid.value('.','INT') AS groupid

FROM @xml.nodes('root') AS groups(gid)

) temp

JOIN @Group g ON g.groupid=CASE WHEN temp.groupid=0 THEN g.groupid ELSE temp.groupid END

JOIN @User_Group ug ON ug.groupid=g.groupid

JOIN @User u ON u.userid=ug.userid

WHERE u.userid = @userid

An alternative though is to handle this situation in Presentation layer such that when user selects 0 (All) it would pass only the values 3,5,9..

Did it produce your expected outcome? I'll be waiting for your response..

Cheers!

OceanDiscovery Old Hand Points: 355 More actions · Answer 4

Thanks for the feedback!

I'm just working through these two options and how I might be able to implement them.

I'll post back what I'm working on as soon as I get time.

OceanDiscovery Old Hand Points: 355 More actions · Answer 5

Thanks shield_21, yes this is similar to my other post (I want to address a similar concept on another database).

I have a list that is populated by a procedure (below).

This returns values to the user based on privileges.

If they have high level (SecCond = 1 in the below procedure), their list might be:

Dpt_GroupIdDescriptionHr_RecordId

0(All)87

132Demo Boning Group87

130Demo Management87

131Demo Slaughter Group87

133Demo Stockyards Group87

otherwise, their list might be

Dpt_GroupIdDescriptionHr_RecordId

132Demo Boning Group87

This list is then used to return records from another table e.g. Audits (not in the procedure) based on their selection. Obviously if they select anything other than (All) the filter on table Audits is straight forward.

However, if they select (All), I only want to return values from table Audits where the Dpt_GroupId is (in this example) 132, 130, 131 or 133.

I have the procedure tidied up (previously it was 4 functions and a procedure); now I'm focussing on a way to correctly return appropriate records from the table Audits. This table has a single field (INT) to filter by.

I really appreciate the help I've been given already; fingers crossed you can help me some more!

Procedure:

DECLARE @User INT, @SecCond INT, @Site INT

DECLARE @dpt3TABLE (Dpt_GroupId INT, GroupId INT, Description VARCHAR(50), Site INT, Room INT)

DECLARE @HrSiteTABLE (Hr_RecordId INT, Hr_Record_SiteId INT)

DECLARE @HrGroupTABLE (Grp INT, Hr_Record_SiteId INT)

-- Configure user options here

-- ordinarily configured by logged on user (can be changed here to view results)

SET @User = 87-- Hr_RecordId FROM @HrSite(1 through 151)

-- selected by user

SET @Site = 17-- Site FROM @dpt3(0; 1 through 20)- 0 selects All

-- configured by logged on user

SET @SecCond = 1-- (0 OR 1)- 0 = Users Group only; 1 = All Groups

-- sample data

INSERT INTO @dpt3

-- (Dpt_GroupId INT, GroupId INT, Description VARCHAR(50), Site INT, Room INT)

SELECT 1, 1, 'Development Studio', 1, NULL UNION ALL

SELECT 2, 1, 'Design Team', 1, 1 UNION ALL

SELECT 128, 128, 'C-Information Technology (IT)', 15, NULL UNION ALL

SELECT 129, 129, 'C-Operations', 15, NULL UNION ALL

SELECT 130, 130, 'Demo Management', 17, NULL UNION ALL

SELECT 131, 131, 'Demo Slaughter Group', 17, NULL UNION ALL

SELECT 132, 132, 'Demo Boning Group', 17, NULL UNION ALL

SELECT 133, 133, 'Demo Stockyards Group', 17, NULL UNION ALL

SELECT 134, 132, 'Demo Bone 1', 17, 37 UNION ALL

SELECT 135, 132, 'Demo Bone 2', 17, 37 UNION ALL

SELECT 136, 133, 'Demo Yard team 1', 17, 39 UNION ALL

SELECT 137, 133, 'Demo Yard team 2', 17, 39 UNION ALL

SELECT 138, 131, 'Demo Gang 1', 17, 38 UNION ALL

SELECT 139, 131, 'Demo Gang 2', 17, 38 UNION ALL

SELECT 140, 128, 'C-Development', 15, 37 UNION ALL

SELECT 141, 130, 'Demo Management Team', 17, 44 UNION ALL

SELECT 142, 130, 'Demo QA Team', 17, 44 UNION ALL

SELECT 151, 151, 'C-EMS (Environmental)', 15, NULL UNION ALL

SELECT 152, 152, 'C-OH&S', 15, NULL UNION ALL

SELECT 175, 175, 'C-QA', 15, NULL UNION ALL

SELECT 176, 175, 'C-Verification', 15, 43

-- sample data

INSERT INTO @HrSite

-- (Hr_RecordId INT, Hr_Record_SiteId INT)

SELECT 1, 1 UNION ALL

SELECT 6, 4 UNION ALL

SELECT 11, 11 UNION ALL

SELECT 56, 58 UNION ALL

SELECT 57, 59 UNION ALL

SELECT 58, 60 UNION ALL

SELECT 59, 61 UNION ALL

SELECT 60, 62 UNION ALL

SELECT 62, 64 UNION ALL

SELECT 63, 65 UNION ALL

SELECT 64, 66 UNION ALL

SELECT 65, 67 UNION ALL

SELECT 67, 69 UNION ALL

SELECT 68, 70 UNION ALL

SELECT 69, 71 UNION ALL

SELECT 70, 72 UNION ALL

SELECT 71, 73 UNION ALL

SELECT 72, 74 UNION ALL

SELECT 73, 75 UNION ALL

SELECT 74, 76 UNION ALL

SELECT 75, 77 UNION ALL

SELECT 76, 78 UNION ALL

SELECT 77, 79 UNION ALL

SELECT 78, 80 UNION ALL

SELECT 79, 81 UNION ALL

SELECT 80, 82 UNION ALL

SELECT 81, 83 UNION ALL

SELECT 82, 84 UNION ALL

SELECT 83, 85 UNION ALL

SELECT 84, 86 UNION ALL

SELECT 85, 87 UNION ALL

SELECT 86, 88 UNION ALL

SELECT 87, 89 UNION ALL

SELECT 88, 90 UNION ALL

SELECT 89, 91 UNION ALL

SELECT 90, 92 UNION ALL

SELECT 91, 93 UNION ALL

SELECT 92, 94 UNION ALL

SELECT 93, 95 UNION ALL

SELECT 94, 96 UNION ALL

SELECT 95, 97 UNION ALL

SELECT 96, 98 UNION ALL

SELECT 97, 99 UNION ALL

SELECT 98, 100 UNION ALL

SELECT 99, 101 UNION ALL

SELECT 100, 102 UNION ALL

SELECT 101, 103 UNION ALL

SELECT 102, 104 UNION ALL

SELECT 103, 105 UNION ALL

SELECT 104, 106 UNION ALL

SELECT 105, 107 UNION ALL

SELECT 106, 108 UNION ALL

SELECT 107, 109 UNION ALL

SELECT 108, 110 UNION ALL

SELECT 109, 111 UNION ALL

SELECT 110, 112 UNION ALL

SELECT 111, 113 UNION ALL

SELECT 137, 114 UNION ALL

SELECT 137, 115 UNION ALL

SELECT 137, 116 UNION ALL

SELECT 146, 118 UNION ALL

SELECT 146, 120 UNION ALL

SELECT 149, 123 UNION ALL

SELECT 150, 124 UNION ALL

SELECT 151, 125

-- sample data

INSERT INTO @HrGroup

-- (Grp INT, Hr_Record_SiteId INT)

SELECT 2, 1 UNION ALL

SELECT 2, 11 UNION ALL

SELECT 140, 58 UNION ALL

SELECT 141, 59 UNION ALL

SELECT 136, 60 UNION ALL

SELECT 134, 61 UNION ALL

SELECT 134, 62 UNION ALL

SELECT 134, 64 UNION ALL

SELECT 135, 65 UNION ALL

SELECT 142, 66 UNION ALL

SELECT 137, 67 UNION ALL

SELECT 139, 69 UNION ALL

SELECT 139, 70 UNION ALL

SELECT 138, 71 UNION ALL

SELECT 138, 72 UNION ALL

SELECT 137, 73 UNION ALL

SELECT 135, 74 UNION ALL

SELECT 134, 75 UNION ALL

SELECT 135, 76 UNION ALL

SELECT 136, 77 UNION ALL

SELECT 138, 78 UNION ALL

SELECT 142, 79 UNION ALL

SELECT 138, 80 UNION ALL

SELECT 135, 81 UNION ALL

SELECT 135, 82 UNION ALL

SELECT 134, 83 UNION ALL

SELECT 141, 84 UNION ALL

SELECT 138, 85 UNION ALL

SELECT 136, 86 UNION ALL

SELECT 134, 87 UNION ALL

SELECT 138, 88 UNION ALL

SELECT 135, 89 UNION ALL

SELECT 139, 90 UNION ALL

SELECT 135, 91 UNION ALL

SELECT 135, 92 UNION ALL

SELECT 138, 93 UNION ALL

SELECT 136, 94 UNION ALL

SELECT 134, 95 UNION ALL

SELECT 139, 96 UNION ALL

SELECT 139, 97 UNION ALL

SELECT 171, 98 UNION ALL

SELECT 136, 99 UNION ALL

SELECT 168, 100 UNION ALL

SELECT 138, 101 UNION ALL

SELECT 134, 102 UNION ALL

SELECT 137, 103 UNION ALL

SELECT 139, 104 UNION ALL

SELECT 134, 105 UNION ALL

SELECT 139, 106 UNION ALL

SELECT 139, 107 UNION ALL

SELECT 138, 108 UNION ALL

SELECT 134, 109 UNION ALL

SELECT 138, 111 UNION ALL

SELECT 136, 110 UNION ALL

SELECT 176, 112 UNION ALL

SELECT 193, 113 UNION ALL

SELECT 134, 118 UNION ALL

SELECT 236, 123 UNION ALL

SELECT 228, 124 UNION ALL

SELECT 134, 125

-- If the SecCond > 0 add a row for (All)

SELECT 0 AS Dpt_GroupId, '(All)' AS Description, @User AS Hr_RecordId WHERE @SecCond > 0

UNION ALL

-- If the SecCond > 0 show all Group rows (otherwise only show the Users Groups)

SELECT Dpt_GroupId, Description, @User AS Hr_RecordId

FROM @dpt3 AS dpt3

WHERE (@SecCond > 0) AND (Room IS NULL) AND (Site = @Site) OR

(@SecCond > 0) AND (Room IS NULL) AND (@Site = 0)

UNION ALL

-- If the SecCond = 0 Select the Group(s) for Current User

SELECT dpt2.Dpt_GroupId, dpt2.Description, SelectGroupMin.Hr_RecordId

FROM (-- Select the Group(s) for all Users

SELECT Hr_RecordId, dpt1.Dpt_GroupId, dpt1.GroupId

FROM @HrSite hrS INNER JOIN

@HrGroup hrG INNER JOIN

@dpt3 AS dpt1 ON Grp = dpt1.Dpt_GroupId ON hrS.Hr_Record_SiteId = hrG.Hr_Record_SiteId

GROUP BY Hr_RecordId, dpt1.GroupId, dpt1.Dpt_GroupId) AS SelectGroupMin INNER JOIN

@dpt3 AS dpt2 ON SelectGroupMin.GroupId = dpt2.Dpt_GroupId

WHERE @SecCond = 0 AND ((dpt2.Site = @Site) AND (dpt2.Site > 1) AND (SelectGroupMin.Hr_RecordId = @User) OR

(dpt2.Site > 1) AND (SelectGroupMin.Hr_RecordId = @User) AND (@Site = 0))

ORDER BY Description

Data is from the development database as it was doing my head in creating test entries to post! My apologies for the length of the @HrSite and @HrGroup temp tables; but figuring out which records could be deleted without affecting the returned result would be a mission (other tables related to and determining the data are not included here, but don't impact on this procedure)

OceanDiscovery Old Hand Points: 355 More actions · Answer 6

Where I create a value for (All), SELECT 0 AS Dpt_GroupId, '(All)' AS Description, @User AS Hr_RecordId WHERE @SecCond > 0

is it possible to replace the value of '0' with a string of the Id's from the output?

So that in this example Dpt_GroupId Description Hr_RecordId

0 (All) 87

132 Demo Boning Group 87

130 Demo Management 87

131 Demo Slaughter Group 87

133 Demo Stockyards Group 87

the Id of 0 would actually be 132, 130, 131, 133

My thinking is that would this then allow me to use any parameter from this list. Could I create a WHERE clause to handle a value like this to return records from my table Audits?

Something along the lines of:

WHERE GroupId IN (@Group) but to handle the value of @Group as 132, 130, 131, 133, or any single value if selected?

OceanDiscovery Old Hand Points: 355 More actions · Answer 7

1) I've managed to get to the point where I can select "ALL" records from a table, or only those for which a user has security for (partial 'All').

2) What I'd now like to do is handle the option of the user selecting just one specific department. i.e. instead of seeing "All" departments records, just those for one particular department.

For (1) I have:

DECLARE

@UserINT

, @SecCondINT

, @SiteINT

, @GroupIdINT

SET @User= 87

SET @SecCond= 1

SET @Site= 17

SET @GroupId= 0

-- CTE

; WITH CTEDptGroups AS

(

-- If the SecCond > 0 show all Group rows (otherwise only show the Users)

SELECT Dpt_GroupId, Description, @User AS Hr_RecordId

FROM DB_Dpt_Group AS dpt3

WHERE (@SecCond > 0) AND (Room IS NOT NULL) AND (Site = @Site) OR

(@SecCond > 0) AND (Room IS NULL) AND (@Site = 0)

UNION ALL

-- If the SecCond = 0 Select the Current Users Team(s)

SELECT dpt2.Dpt_GroupId, dpt2.Description, MyTeams.Hr_RecordId

FROM

(

SELECT HR_Record_Site.Hr_RecordId, dpt1.Dpt_GroupId

FROM HR_Record_Site INNER JOIN

HR_Record_Grp INNER JOIN

DB_Dpt_Group AS dpt1 ON HR_Record_Grp.Grp = dpt1.Dpt_GroupId ON HR_Record_Site.Hr_Record_SiteId = HR_Record_Grp.Hr_Record_SiteId

) AS MyTeams

INNER JOIN

DB_Dpt_Group AS dpt2 ON MyTeams.Dpt_GroupId = dpt2.Dpt_GroupId

WHERE (@SecCond = 0) AND (dpt2.Site = @Site) AND (dpt2.Site > 1) AND (MyTeams.Hr_RecordId = @User) OR

(@SecCond = 0) AND (dpt2.Site > 1) AND (MyTeams.Hr_RecordId = @User) AND (@Site = 0)

)

SELECT InS_Record.InS_RecordId, InS_Record.Site, InS_Record.Date, InS_Record.Product, InS_Record.Team, DB_Dpt_Group.GroupId

FROM InS_Record INNER JOIN

DB_Dpt_Group ON InS_Record.Team = DB_Dpt_Group.Dpt_GroupId

WHEREDpt_GroupId IN (SELECT Dpt_GroupId FROM CTEDptGroups)

For (2) if @GroupId = 0 records will be selected as above; if @GroupId = x the table needs to return records where GroupId = @GroupId instead.

Any help would be much appreciated as this has already tidied up a few issues.

Thanks