June 8, 2010 at 2:21 pm
hi every one well new to this as i've recently become a cripple.but i have a little query I'm trying to work on and none of the so called experts in the office I use for studying in have an easer answer.
so i have 3 tables [names,tied,rolls] in the names table i have columns [userid] [int]and [names]nvacher] /in the tied table i have [userid] [int]/[rollnum][int]/in the roll table i have [rollnum][int]/[rolls][navchar]
in the tied table
each rollnum has a value which is 1 to five and a uersid which is able to have all five rollnum so i can have up to five identical userid but different rolls
eg
userid | RollNum
1000 | 1
1000 | 2
1000 | 3
1001 | 2
1001 | 5
what i would like to achieve that if i search the tied table i get only one user id and the lowest rollnum
for all the different userid in the tables
i have joined the other 2 tables to the tied table to get all result
so i should get eg
names.name | tied.userid | tied.Rollnum | roll.rolls
john | 1000 | 1 | admin
phil | 1001 | 2 | support
i hope some one can point me in the right cheers
😛
June 8, 2010 at 2:48 pm
I mocked up the tables and data and have provided a solution but I would warn that this may not be that simple unless the data is really that simple. you may need to do a left join or other modifications.
declare @names table
(userid int, Firstnm varchar(20), job varchar(20))
insert into @names (userid,Firstnm,job) values(1000,'John','admin')
insert into @names (userid,Firstnm,job) values(1001,'Phil','support')
declare @tied table
(userid int, rollnum int)
insert into @tied (userid, rollnum) values(1000,1)
insert into @tied (userid, rollnum) values(1000,2)
insert into @tied (userid, rollnum) values(1000,3)
insert into @tied (userid, rollnum) values(1001,2)
insert into @tied (userid, rollnum) values(1001,5)
declare @roll table
(rollnum int, roll varchar(20))
insert into @roll (rollnum,roll) values(1,'Admin')
insert into @roll (rollnum,roll) values(2,'Support')
select a.Firstnm,t.userid,t.rollnum,b.roll
from
(select userid,min(rollnum) as rollnum from @tied group by userid) t
join @names a on a.userid = t.userid
join @roll b on b.rollnum = t.rollnum
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 8, 2010 at 3:00 pm
cheers i'll give it a go tomorrow
there are only four columns to find the information to return, but there will be quite a few userid more than 2 but only 5 different kind of user rolls just as long as it shows all the individual userid and the lowest rollnum then thats all i need:-)
June 9, 2010 at 4:09 pm
hi all again still not quite right, I think all need to be able to do in the tied table is to get the all the distinct userid tied in with there min rollnum eg 1000/1 : 1001/ 3 hope someone can look at this and piont me in the right direction . also as there are five dirrent possible jobs would i have to five new tales ad jion them all together through five quries 😛
June 10, 2010 at 7:24 am
I quess I am a little confused by you rlast post. The script I provided should do what you have stated unless there are data elements I am not aware of. Go you post some sample data that describes the problem you are having? that might help determine where the change needs to be. It does not have to be live data just examples that demonstraight what you are trying to aceive and what problem you are having.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 23, 2010 at 2:56 pm
I would be tempted to do this a little differently. I am not certain what the efficiency impact would be, but it seems to be easier to see what it does and how.
Using the structures described:
WITH GetMinRoleByUser_CTE AS
(SELECT userid, MIN(rollnum) AS MinRole FROM tied GROUP BY userid)
SELECT
n.UserID,
n.names,
r.rollnum,
r.rolls
FROM
GetMinRoleByUser_CTE c
LEFT JOIN names n
ON c.userid = n.userid
LEFT JOIN rolls r
ON c.MinRoll = r.rollnum
ORDER BY n.names
I believe that this approach makes it a little more obvious what data is being returned, and also very straightforward to sort it by user or by role. It also shows if there are records in tied where the user and/or the role
does not exist.What this does not do is show users not assigned to any role, or roles with no users assigned.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply