Hello

I'm trying to get this to work, i dont get the exists/join thing to work at
all....



CREATE TABLE #Threads
(
  MsgID int,
  RootMsgID int,
  Foo char(1)
)

CREATE TABLE #Test
(
  MsgID int,
  UserID int
)



INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(1,1,'A')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(2,1,'B')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(3,1,'C')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(4,1,'D')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(5,5,'E')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(6,6,'F')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(7,6,'G')
INSERT INTO #Threads(MsgID,RootMsgID,Foo)VALUES(8,8,'H')

INSERT INTO #Test(MsgID,UserID)VALUES(1,66)
INSERT INTO #Test(MsgID,UserID)VALUES(2,66)
INSERT INTO #Test(MsgID,UserID)VALUES(3,66)
INSERT INTO #Test(MsgID,UserID)VALUES(4,66)
INSERT INTO #Test(MsgID,UserID)VALUES(5,66)
INSERT INTO #Test(MsgID,UserID)VALUES(6,66)


SELECT *,
hasNewMsgs=(
   /* some code here */
   )
  FROM #Threads T WHERE T.MsgID = T.RootMsgID


/*
Desired result  0 = user has read all msgs in whole thread, 1 = all or some
new messages

MsgID RootMsgID Foo hasNewMsgs
1  1  A  0
5  5  E  0
6  6  F  1
8  8  H  1

*/

DROP TABLE #Threads
DROP TABLE #Test

it's a bit convoluted, but what about this:

SELECT T.MsgID, T.RootMsgID, T.Foo, CASE SUM(CASE WHEN u.msgID IS NULL
THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE 1 END  as hasNewMsgs
FROM #Threads T
LEFT JOIN #Threads as children on children.RootMsgID = T.MSGID
LEFT JOIN  #test as u on u.msgID = children.msgid
WHERE t.MsgID = t.RootMsgID
GROUP BY T.MsgID, T.RootMsgID, T.Foo

Newbie - Query with loop?
Float vs Decimal
Line item query
Fill in missing date ranges
Group by and Case Statements