February 15, 2010 at 9:56 pm
Hi ,
I have got a requirement.below are the details.
I have 3 tables
Event (Id,Name,StartDate),
User(Id,Name),
Workscope(Id,Fk_User,Fk_Event)
sample data :
Event
Id Name StartDate
1 E1 02/12/2010
2 E2 02/12/2010
3 E3 02/12/2010
4 E4 02/12/2010
5 E5 02/12/2010
User
Id Name
1 U1
2 U2
WorkScope
Id Fk_User Fk_Event
1 1 1
2 1 2
3 1 3
Now my requirement is :
If the user 1(U1) logs into the system i shud get only three events which are mapped to that user in workscope table (please see workscope table sample data)
If the User 2(U2) logs in all the events shud come that is from all the events in the event table.
Means :
For User 1(U1)
Event Id
1
2
3
For User 2(U2)
Event Id
1
2
3
4
5
one thing i need to integrate this query with some other query so IF ELSE i cant use.
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
February 15, 2010 at 11:11 pm
See if this works...
DECLARE @strUserName VARCHAR(100)
SET @strUserName = 'U2'
; WITH cteUserMapping AS
(
SELECT*
FROMUser U
WHERE EXISTS( SELECT * FROM WorkSpace W WHERE W.FK_User = U.ID )
), cteUserMappingDetails AS
(
SELECT*
FROMEvent
WHERE NOT EXISTS( SELECT * FROM cteUserMapping WHERE Name = @strUserName )
UNION ALL
SELECTE.*
FROMWorkSpace W
INNER JOIN Event E ON W.FK_Event = E.ID
INNER JOIN User U ON W.FK_User = U.ID AND U.Name = @strUserName
)
SELECT * FROM cteUserMappingDetails
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 15, 2010 at 11:23 pm
Hi ,
This works pretty good , But i have to integrate with my query like i have a query for that i need to add a where condition saying
where Fk_Event in (CTE or any code which shud return EventId's) can you please let me know how can i change your script accordingly
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
February 15, 2010 at 11:31 pm
Hi ,
Thanq i made the final query , integrating with yours . One small ques will it be faster for huge data coz this i need in reports where already the query for that report is huge so it shud add much burden on the entire query. see i found one solution but that is using IF ELSE not in query but just like a flag please check the below code
Declare @flag int
if exists (select 1 from WorkScope where FK_User=2)
set @flag=0
else set @flag=1
select E.Id
from Event E
left outer join WorkScope WE on WE.FK_Event=E.Id and WE.FK_User=2
where isnull(WE.Id,0) =(case when @flag=1 then 0 else WE.Id end)
This also working fine but iam using IF ELSE before my query.
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
February 16, 2010 at 2:05 am
Yes. It should work fine even for larger tabes with proper indexes. But i would suggest you to test with proper data before coming to any conclusion.. Your query would work fine too..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 16, 2010 at 2:20 am
Thanq very much for the response and for giving me a nice idea of uisn CTE's which will surely help me further
/********************************************************
Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
********************************************************/
February 16, 2010 at 2:43 am
Glad that i could help you out...:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply