Select Result From two 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

General SQL Server Forums

New to SQL Server Programming

Select Result From two Tables

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-17 : 01:12:13

Hi friends,

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

CREATE TABLE [LP_UserManagement] (
[UserId] [bigint] NOT NULL ,
[Email] [varchar] (128) NOT NULL ,
[NickName] [varchar] (128) NOT NULL
)

insert into LP_UserManagement VALUES(2,'sambath.ekambaram@g.com','sambath')
insert into LP_UserManagement VALUES(3,'dhina@g.com','dhina')
insert into LP_UserManagement VALUES(25,'kotti@g.com','kotti')
insert into LP_UserManagement VALUES(89,'expert01@g.com','expert01')
insert into LP_UserManagement VALUES(89,'expert02@g.com','expert02')

CREATE TABLE [LP_Mail] (
[MailId] [bigint] NOT NULL ,
[ParentId] [bigint] NULL,
[FromUserId] [bigint] NOT NULL ,
[ToUserId] [bigint] NOT NULL ,
[Subject] [varchar] (1000) NULL ,
[Text] [varchar] (1000) NULL ,
[Attachafile] [varchar] (128) NULL ,
[Flag] [bit] NOT NULL,
)

insert into LP_Mail VALUES (1,0,2,3,'Test1','Test2','',1)
insert into LP_Mail VALUES (2,0,2,3,'Test3','Test4','',1)
insert into LP_Mail VALUES (3,1,3,2,'Test5','Test6','',1)
insert into LP_Mail VALUES (4,1,2,3,'Test7','Test8','',1)
insert into LP_Mail VALUES (5,0,2,89,'Test9','Test10','upimages/sam.doc',1)
insert into LP_Mail VALUES (6,5,89,2,'Test11','Test12','',1)
insert into LP_Mail VALUES (7,5,2,89,'Test13','Test14','',1)
insert into LP_Mail VALUES (8,5,89,2,'Test15','Test16','',1)
insert into LP_Mail VALUES (9,5,2,89,'Test17','Test18','',1)
insert into LP_Mail VALUES (10,0,2,90,'Test19','Test20','',1)
insert into LP_Mail VALUES (11,10,90,2,'Test21','Test22','',1)
insert into LP_Mail VALUES (12,10,2,90,'Test23','Test24','',1)
insert into LP_Mail VALUES (13,5,89,2,'Test25','Test26','upimages/pic1.jpg',0)

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.

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 'Yes'
WHEN (SELECT TOP 1 Flag FROM LP_mail WHERE ParentId=M.MailId)=0 THEN 'No'
WHEN (SELECT Top 1 Flag FROM LP_mail WHERE ParentId=M.MailId)=1 THEN 'Yes' END) as MailRead,
(select Email from LP_Usermanagement where UserId=2) Email
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

Expected Results

Email SubjectText(count of submails) MailRead Attachafile
sambath.ekambaram@g.com Test1(2) Yes
sambath.ekambaram@g.com Test3(5) No upimages/sam.doc
sambath.ekambaram@g.com Test9(0) Yes
sambath.ekambaram@g.com Test19(2) Yes

If U need more explanations , i will explain

please help this concern

Thanks an Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 01:46:24

try


select	u.Email, 
	m.Subject, 
	cnt 		= count(c.ParentId),
	MailRead	= case when max(convert(int, m.Flag)) + max(convert(int, c.Flag)) <> 0 then 'Yes' else 'No' end,
	Attachafile 	= max(m.Attachafile)
from	LP_UserManagement u
	inner join LP_Mail m	on	u.UserId	= m.FromUserId
				and	m.ParentId	= 0
	left join LP_Mail c	on	c.ParentId	= m.MailId
group by u.Email, m.Subject

KH
[spoiler]Time is always against us[/spoiler]

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-07-17 : 02:52:41

Khan ,

Thanks for your reply.
But i didn't get correct answer.I get the below answer when i execute the query given by you.
Email Subject cnt mailread attachafile
sambath.ekambaram@g.com Test1 2 Yes
sambath.ekambaram@g.com Test19 2 Yes
sambath.ekambaram@g.com Test3 0 No
sambath.ekambaram@g.com Test9 5 Yes upimages/sam.doc

In the third row i am getting the wrong answer in the mailread field ,since the mailid for that row is 2 and when we have flag for the mailid as true so it should return as Yes in the mailread field.
In the Fourth row the mailread field is wrong.Since the mailid for that row is 5 and if we check for flag for that mailid 5 it is true but the mailid 5 should also be checked in parentId so in mailid 13 ,the flag is false .So it should return as No

If i confused you, i will explain much better.
Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 03:04:24

[code]
select u.Email,
m.Subject,
cnt = count(c.ParentId),
MailRead = case when min(convert(int, m.Flag)) * min(convert(int, isnull(c.Flag, 1))) = 0 then 'No' else 'Yes' end,
Attachafile = max(m.Attachafile),
min(convert(int, m.Flag)) + min(convert(int, isnull(c.Flag, 1)))
from LP_UserManagement u
inner join LP_Mail m on u.UserId = m.FromUserId
and m.ParentId = 0
left join LP_Mail c on c.ParentId = m.MailId
group by u.Email, m.Subject
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:36:24

can there be more than two levels?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources