SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query on Parent Child messages
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/29/2013 :  04:35:15  Show Profile  Reply with Quote
Hi,

I have one table Messages which holds parent and its child messages.

Table:

mid  parentid  subject      msgbody
1      null      main         test
2        1       subreply      ..
3      null      test          ..
4        1       subreply      ..


So here, output should be like

mid    parentid    subject    msgbody   counts
4         1        subreply    ..         3
3        null      test        ..         0 


I tried with query below for not exists but confused with count and fetching only latest record from it.


NOT EXISTS (SELECT 1 FROM tblMessages MInner WHERE MInner.ParentId = M.MessageId)


Please help me out on this query.

Edited by - keyursoni85 on 03/29/2013 05:52:14

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 03/29/2013 :  04:55:58  Show Profile  Reply with Quote
how come the count is 3 for mid 4?
can you explain the logic?
why your output doesn't have MIDs( 1, 2)?
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/29/2013 :  05:51:51  Show Profile  Reply with Quote
Hi,

In my output, message id 1 and 2 is not available because I want to display recent and latest message in my messages queue.

In my sample data message queue becomes for messageid 1. and total 3 counts for 3 messages which I want to display.

Edited by - keyursoni85 on 03/29/2013 05:52:47
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 03/29/2013 :  06:22:50  Show Profile  Reply with Quote

--Your sample data script
DECLARE @Msgs TABLE(mid  INT, parentid INT, subject  varchar(30), msgbody varchar(30))
INSERT INTO @Msgs
SELECT 1, null, 'main', 'test' union all
SELECT 2, 1, 'subreply', 'afasj'union all
SELECT 3, null, 'test', 'afasj' union all
SELECT 4, 1, 'subreply', 'afasj'

SELECT mid, parentid, subject, msgbody
		,case when parentid IS null then 0 
			  else dense_Rank() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs


Note: @Msgs sholud be your table name
EDIT: use TOP 2 clause based on mid descending for selecting latest messages

Edited by - bandi on 03/29/2013 06:33:21
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 03/29/2013 :  06:36:17  Show Profile  Reply with Quote
SELECT TOP 2 mid, parentid, subject, msgbody
		,case when parentid IS null then 0 
			  else dense_RAnk() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs 
ORDER BY mid DESC
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/29/2013 :  06:57:59  Show Profile  Reply with Quote
Invalid output in case of below data..


DECLARE @Msgs TABLE(mid  INT, parentid INT, subject  varchar(30), msgbody varchar(30))
INSERT INTO @Msgs
SELECT 1, null, 'main', 'test' union all
SELECT 2, 1, 'subreply', 'afasj'union all
SELECT 3, null, 'test', 'afasj' union all
SELECT 4, 1, 'subreply', 'afasj' union all
SELECT 5, 1, 'subreply', 'afasjser'

SELECT TOP 2 mid, parentid, subject, msgbody
		,case when parentid IS null then 0 
			  else dense_RAnk() over(order by case when parentid is null then 0 else mid end) end cnt
FROM @Msgs 
ORDER BY mid DESC

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 03/29/2013 :  07:33:39  Show Profile  Reply with Quote
what should be the output for above sample data?
i thought the count should be number of rows before that particular record...

Whats your logic behind the count?
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 03/30/2013 :  01:20:51  Show Profile  Reply with Quote
Output is already given.

Suppose I have 3 Parent messsages and out of those three messages.. I have 5 child messages on first parent message.

So, Last child message of that parent and other two parent messages should be displayed.

In short, Most recent message of queue and if no child message then display parent message.


mid  parentid  subject      msgbody
1      null      main         test
2        1       subreply      ..
3      null      test          ..
4        1       subreply      ..
5        1       5subreply     ..
6      null      parent 6      ..
7        6       6thchild      ..


Output

mid    parentid    subject    msgbody   counts
5         1        5subreply    ..         4 (Recent message on queue)
3        null      test        ..          0 (No child messages)
7         6         6thchild   ..          2 (6th recent child message)

Edited by - keyursoni85 on 03/30/2013 01:21:39
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 04/03/2013 :  08:34:28  Show Profile  Reply with Quote
Any reply on this to make it done.. visakh or bandi?
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
280 Posts

Posted - 04/03/2013 :  12:05:00  Show Profile  Reply with Quote
not the best approach ,but try it


select
T1.mid
,T1.parentid
,T1.subject
,T1.msgbody
,coalesce(TC.NoCounts,0) as NoCounts
from YourTable T1
outer apply
(select top 1 coalesce(parentID,mID) as ID
,mID as mid
from YourTable as T2
where coalesce(T1.parentID,T1.mID)=coalesce(T2.parentID,T2.mID)
order by mid desc) as TR
outer apply
(select top 1 coalesce(parentID,mID) as ID
,count(coalesce(parentID,mID)) as NoCounts
from YourTable as TC
where coalesce(T1.parentID,T1.mID)=coalesce(TC.parentID,TC.mID)
group by coalesce(parentID,mID)
Having count(coalesce(parentID,mID))>1
) as TC
where t1.mid=tr.mid
order by coalesce(T1.parentID,T1.mID)


S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Edited by - stepson on 04/03/2013 12:07:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000