1. Find IMs containing specific word. SELECT * FROM DatabaseName.dbo.Messages where Body like '% search-word %' 2. Find all IMs sent by a user. SELECT * from DatabaseName.dbo.Messages, DatabaseName.dbo.Users where DatabaseName.dbo.Users.UserId = DatabaseName.dbo.Messages.FromId and DatabaseName.dbo.Users.UserUri = ‘user-you-are-looking-for’ 3. Find all IMs received by a user. SELECT * from DatabaseName.dbo.Messages, DatabaseName.dbo.Users where = DatabaseName.dbo.Users.UserIdDatabaseName.dbo.Messages.ToId and DatabaseName.dbo.Users.UserUri = ‘user-you-are-looking-for’ 4. Find IM body, sender and receiver information from all IMs exchanged between two users SELECT body, u1.UserUri as [from], u2.UserUri as [to] from DatabaseName.dbo.Messages, DatabaseName.dbo.Users u1, DatabaseName.dbo.Users u2 where DatabaseName.dbo.Messages.FromId = u1.UserId and DatabaseName.dbo.Messages.ToId = u2.UserId and u1.UserUri = ‘first-user’ and u2.UserUri = ‘second user’ UNION ALL SELECT body, u1.UserUri , u2.UserUri from DatabaseName.dbo.Messages, DatabaseName.dbo.Users u1, DatabaseName.dbo.Users u2 where DatabaseName.dbo.Messages.FromId = u2.UserId and DatabaseName.dbo.Messages.ToId = u1.UserId and u2.UserUri = ‘first-user’ and u1.UserUri = ‘second user’ 5. Find all IMs sent between certain time range SELECT * from dbo.Messages where SessionIdTime > '2010-05-10' and SessionIdTime < '2010-05-17' 6. Find all IMs sent by a user within time range SELECT * from dbo.Messages where FromId ='46' and SessionIdTime > '2010-01-28' and SessionIdTime < '2010-02-04' |
|
Office Communicator >