Simple Query with Complex requirement..

Question

Simple Query with Complex requirement..

Shahzadi

Default port

Points: 1479
More actions
September 8, 2008 at 5:29 am

#212264

i am just stuck in a simple and complex query.. i hope that one of you will surly help me..
i have three table in my Database (UserTeam, Team, User) and i need to have the list of Those user which Disabled = False
i have UserTeam which save each Record Seprately..when it's been updated through program...
i have attached the tables like this
UserTeam
UserID TeamID Disabled Created
1 1 FALSE 01/08/2008
1 1 TRUE 15/08/2008
1 1 FALSE 23/08/2008
2 2 FALSE 01/08/2008
3 1 FALSE 05/08/2008
3 1 TRUE 19/08/2008
Team
TeamID TeamName
1 Team1
2 Team2
Users
UserID UserName
1 User1
2 User2
3 User3
from this table i need the following result on behalf of Disabled, if the last disabled of the specific user is false then return the result otherwise No.. from the following table i need the following result..
UserID UserName TeamName
1 User1 Team1
2 User2 Team2
i hope i would b able to convay my resutl

Viewing 6 posts - 1 through 5 (of 5 total)

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

arjun.tewari SSC Eights! Points: 963 More actions · Answer 1

Shahzadi (9/8/2008)
i have three table in my Database (UserTeam, Team, User) and i need to have the list of Those user which Disabled = False
i have UserTeam which save each Record Seprately..when it's been updated through program...
i have attached the tables like this
UserTeam
UserID TeamID Disabled Created
1 1 FALSE 01/08/2008
1 1 TRUE 15/08/2008
1 1 FALSE 23/08/2008
2 2 FALSE 01/08/2008
3 1 FALSE 05/08/2008
3 1 TRUE 19/08/2008
Team
TeamID TeamName
1 Team1
2 Team2
Users
UserID UserName
1 User1
2 User2
3 User3
from this table i need the following result on behalf of Disabled, if the last disabled of the specific user is false then return the result otherwise No.. from the following table i need the following result..
UserID UserName TeamName
1 User1 Team1
2 User2 Team2

Following query will help:

select a.userid, b.username, c.teamname from userteam a join

(select userid, teamid, max(created) created from userteam group by userid, teamid)x on a.userid = x.userid and a.teamid = x.teamid and a.created = x.created and a.disabled = 'FALSE' join team b on x.teamid = b.teamid join user c on x.userid = c.userid

Shahzadi Default port Points: 1479 More actions · Answer 2

Thanks for your prompt reply..

it's only returning the first row of result table..

i have checked into userteam table. that the record that has only one row. it's not returning anything, but if it's got more then one row it's return the result .. any idea ?

nkm129 SSCommitted Points: 1763 More actions · Answer 3

small correction...

Hope this works....

select a.userid, c.username, b.teamname from userteam a join

(select userid, teamid, max(created) created from userteam group by userid, teamid)x

on a.userid = x.userid and a.teamid = x.teamid and a.created = x.created and a.disbaled = '0'

join team b on x.teamid = b.teamid

join users c on x.userid = c.userid

Thanks!

arjun.tewari SSC Eights! Points: 963 More actions · Answer 4

:Dactually I am at a mobile device and cannot test the queries.... just wrote the logic.

Shahzadi Default port Points: 1479 More actions · Answer 5

any way thanks for your reply.. atleast i got the idea. now i will try to do by myown self.. thanks......