How Get Values from Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How Get Values from Tables

Author

Topic

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-13 : 05:51:03

Hi,
I am using three tables usermgmt,countrymgmt and userpremiummgmt

usermgmt tables contains following values

userid username email countryid status deleted

1 karthik kart@yahoo.com 1 1 0
2 kotti kotti@yahoo.com 2 1 0
3 raj raj@yahoo.com 3 1 0
4 kumar kumar@yahoo.com 1 1 0
countrymgmt tables contains following values

countryid cname
1 India
2 Pakistan
3 srilanga

userpremiummgmt tables contains following values

premiumid userid paymentstatus creationtime

1 1 Active Jan 1 2010
2 1 Failed Jan 2 2010
3 1 Active Jan 3 2010
4 2 Active Jan 4 2010
5 2 Failed Jan 5 2010
6 3 Failed Jan 6 2010
7 3 Active Jan 7 2010

here we select recent premiumid for particular userid

I need following values
userid name email paymentstatus country

1 karthik kart@yahoo.com Active India
2 kotti kotti@yahoo.com Failed Pakistan
3 raj raj@yahoo.com Active Srilanka
4 kumar kumar@yahoo.com null India

I need following values based on paymentstatus is active

userid name email paymentstatus country

1 karthik kart@yahoo.com Active India
3 raj raj@yahoo.com Active Srilanka

I need following values based on paymentstatus is Failed

userid name email paymentstatus country

2 kotti kotti@yahoo.com Active Pakistan

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 05:56:47

[code]select u.userid, u.username,u.email,p.paymentstatus,c.country
from usermgmnt u
join countrymgmnt c
on c.countryid=u.countryid
join userpremiummgmnt p
on p.userid=u.userid
join (select userid,max(creationtime) as latest
from userpremiummgmnt
group by userid)p1
on p1.userid=p.userid
and p1.latest=p.creationtime
where p.paymentstatus = @status

pass @status as active,failed etc to get relevant records
[/code]

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-13 : 06:22:57

Hi,
Thanks for your reply.

I tried to use this query it gives following result

userid name email paymentstatus country

1 karthik kart@yahoo.com Active India
3 raj raj@yahoo.com Active Srilanka

also i used where condition for status=1 and deleted=0
it gives

userid name email paymentstatus country

3 raj raj@yahoo.com Active Srilanka

Thanks in Advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources