Getting Result From two Tables

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

General SQL Server Forums

New to SQL Server Programming

Getting Result From two Tables

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-15 : 11:24:38

Hi Friends,

I need help to get a query.

LP_Usermanagement

UserId Nickname
2 sambath
3 dhina
25 kotti
89 expert01
90 expert02

LP_Mail

MailId ParentId FromUserId ToUserId Subject Text Attachafile Flag
1 0 2 3 Test1 Test2 1
2 0 2 3 Test3 Test4 1
3 1 3 2 Test5 Test6 0
4 1 2 3 Test7 Test8 1
5 0 2 89 Test9 Test10 upimages/sam.doc 1
6 5 89 2 Test11 Test12 1
7 5 2 89 Test13 Test14 1
8 5 89 2 Test15 Test16 1
9 5 2 89 Test17 Test18 1
10 0 2 90 Test19 Test20 1
11 10 90 2 Test21 Test22 1
12 10 2 90 Test23 Test24 1
13 5 89 2 Test25 Test26 upimages/pic1.jpg 0

Here i have two tables one for maintaining user details and other one for maintaining Email details sent by users

Here when parentid=0 means the user has first sent the email .when anyone reply to this email ,the mailid will be saved in the
parentid field.
For your reference in the above table we will consider the first row.
Mailid field has 1 ,parentId field has o ,and From field has 2 etc
for this email two reply has been there (i,e) MailId 3 row and MailId 4 row which has parentid 1 and if the flag is 0 for anyone
of the row i need to return as IN and If None of the row Flag as 0 i need to return as Out.

If U need more explanations , i will explain

This is my test query

SELECT M.*,(CASE WHEN M.Attachafile<>'' THEN
'<a href='+M.Attachafile+'><img src=upimages/attachment.png height=15 width=20 border=0/></a>' END) as Attached,
M.Subject+' ('+CAST((SELECT count(*) FROM LP_MAIL WHERE ParentId=M.MailId) AS VARCHAR(128))+')' AS SubjectText,
(CASE WHEN (SELECT TOP 1 Flag FROM LP_mail WHERE ParentId=M.MailId) IS NULL THEN 'OUT'
WHEN (SELECT TOP 1 Flag FROM LP_mail WHERE ParentId=M.MailId)=0 THEN 'IN'
WHEN (SELECT Top 1 Flag FROM LP_mail WHERE ParentId=M.MailId)=1 THEN 'OUT' END) as ChkFlag,
(select Nickname from LP_Usermanagement where UserId=2) Nickname,
(select (CASE WHEN Online=1 THEN 'Expert is online!' WHEN Online=0 THEN '' END) from LP_Usermanagement where UserId=2)
Online FROM LP_Mail M WHERE (M.FromUserId=2 OR M.ToUserId=2) and M.ParentId=0 Order by MailId DESC

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 12:37:15

ok. so what should be output that you're looking at? also are you using sql 2005?

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-16 : 00:55:46

Visakh,

Thank you for your Reply,

I am using Sql server 2000,

Please find my expected output below

From Subject ChkFlag Attachafile
----------------------------------------------------------------------------------------
sambath.ekambaram@g.com Test1 (2) OUT
sambath.ekambaram@g.com Test3 (5) IN upimages/sam.doc
sambath.ekambaram@g.com Test9 (0) OUT
sambath.ekambaram@g.com Test19 (2) OUT

Thanks in Advance

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-16 : 04:50:08

Anyone please help me regarding this query.

Thanks in Advance

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-16 : 07:43:40

Hi friends,

Anyone please help me regarding this query.

If U need more explanations , i will explain

please help this concern

Thanks an Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 12:31:21

quote:
Originally posted by esambath

Visakh,

Thank you for your Reply,

I am using Sql server 2000,

Please find my expected output below

From Subject ChkFlag Attachafile
----------------------------------------------------------------------------------------
sambath.ekambaram@g.com Test1 (2) OUT
sambath.ekambaram@g.com Test3 (5) IN upimages/sam.doc
sambath.ekambaram@g.com Test9 (0) OUT
sambath.ekambaram@g.com Test19 (2) OUT

Thanks in Advance

this output doesn't make sense . where did you get email values from? these are not specified in posted data.
suggest you to repost giving data in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources