Confusing Select Statement! have to test 2 fields with 1 parameter
I have this select statement that I have to upgrade.
Basicly the person who is reading this IS identified by the userID paramenter. The User maybe the recipient of the message, but it doesn't mean they started the thread or in otherwords, it doesn't mean they are the original sender or recipient.
I am now updating wether or not the user deleted the thread. But I have to refelect if its been deleted from this select statement.
Is it possible to do this in SQL?
where this and this and not ( (this where this = this) or (this where this = this))
WHERE ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)) AND ((SELECT SenderID FROM ag_threads c WHERE c.Senderid = CAST(@UserID AS UNIQUEIDENTIFIER) AND a.SenderDelete != TRUE) OR (SELECT SenderID FROM ag_threads c WHERE c.Recipientid = CAST(@UserID AS UNIQUEIDENTIFIER) AND a.RecipientDelete != TRUE) )
The Threads Table looks like this!
ThreadId uniqueidentifier Unchecked
SenderId uniqueidentifier Unchecked
RecipientId uniqueidentifier Unchecked
SenderDelete bit Unchecked
RecipientDelete bit Unchecked
If thats not possible, whats a better way to make this happen! My brain hurts!
Basicly the person who is reading this IS identified by the userID paramenter. The User maybe the recipient of the message, but it doesn't mean they started the thread or in otherwords, it doesn't mean they are the original sender or recipient.
I am now updating wether or not the user deleted the thread. But I have to refelect if its been deleted from this select statement.
Is it possible to do this in SQL?
where this and this and not ( (this where this = this) or (this where this = this))
WHERE ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)) AND ((SELECT SenderID FROM ag_threads c WHERE c.Senderid = CAST(@UserID AS UNIQUEIDENTIFIER) AND a.SenderDelete != TRUE) OR (SELECT SenderID FROM ag_threads c WHERE c.Recipientid = CAST(@UserID AS UNIQUEIDENTIFIER) AND a.RecipientDelete != TRUE) )
The Threads Table looks like this!
ThreadId uniqueidentifier Unchecked
SenderId uniqueidentifier Unchecked
RecipientId uniqueidentifier Unchecked
SenderDelete bit Unchecked
RecipientDelete bit Unchecked
If thats not possible, whats a better way to make this happen! My brain hurts!
SELECT ag_messages.ThreadId, ag_threads.Subject, ag_threads.senderDelete, ag_threads.recipientDelete, ag_threads.SenderId, ag_threads.RecipientId, ag_messages.MessageId, ag_messages.SenderId, ag_messages.RecipientId, ag_messages.TimeSent, ag_messages.Message, ag_profiles.RealName
FROM ag_threads
INNER JOIN (ag_profiles INNER JOIN ag_messages ON ag_profiles.[UserId] = ag_messages.[SenderId]) ON ag_threads.[ThreadId] = ag_messages.[ThreadId]
WHERE ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER))
ASKER
I know its confusing me toooo.
So I added more functionality to my website. I allowed people to delete threads from thier inbox.
The delete doesn't actually delete anything - it just updates the bit [sender/recipient]Delete for that user in the Threads table.
Now that needs to be reflected in the select statement which only selects the the last reply message to each thread! But I need it selects the the last reply message to each thread which that user has not deleted.
2 people are sharing the thread as a conversation, I want them to both be able to delete the thread individually and then later I will go delete all the threads which both recipient and sender have delete later!
the problem is, even though its in the inbox, it doesn't mean that the user is the originator of the thread, or the recipient. only that someone has replied to that thread :D
So I added more functionality to my website. I allowed people to delete threads from thier inbox.
The delete doesn't actually delete anything - it just updates the bit [sender/recipient]Delete for that user in the Threads table.
Now that needs to be reflected in the select statement which only selects the the last reply message to each thread! But I need it selects the the last reply message to each thread which that user has not deleted.
2 people are sharing the thread as a conversation, I want them to both be able to delete the thread individually and then later I will go delete all the threads which both recipient and sender have delete later!
the problem is, even though its in the inbox, it doesn't mean that the user is the originator of the thread, or the recipient. only that someone has replied to that thread :D
OK, so where are we with this select? What is this returning / not returning?
SELECT ag_messages.ThreadId, ag_threads.Subject, ag_threads.senderDelete, ag_threads.recipientDelete,
ag_threads.SenderId, ag_threads.RecipientId, ag_messages.MessageId, ag_messages.SenderId, ag_messages.RecipientId,
ag_messages.TimeSent, ag_messages.Message, ag_profiles.RealName
FROM ag_threads
INNER JOIN ag_messages
ON ag_threads.[ThreadId] = ag_messages.[ThreadId]
AND ag_threads.recepientDelete = 0
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER))
AND ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
INNER JOIN ag_profiles
ON ag_profiles.[UserId] = ag_messages.[SenderId]
ASKER
that statement returns all the threads except the ones that the user is the recipient and recipientDelete is true.
so if someone sent me the message, and I deleted it, its still in the inbox
but if i sent someone the message and then I deleted it, its not in the inbox!
so if someone sent me the message, and I deleted it, its still in the inbox
but if i sent someone the message and then I deleted it, its not in the inbox!
ASKER
I tested the SQL statement you posted more, and it doesn't distingust between deleted or not deleted, its just not showing messages that they sent.
The original statement I posted works great, but it doesn't handle not selecting the threads which have been deemed deleted by that user.
That is all that I need to add to the sql statement :D
The original statement I posted works great, but it doesn't handle not selecting the threads which have been deemed deleted by that user.
That is all that I need to add to the sql statement :D
ASKER
I think I am going to try to take the UNION approach and just write 2 different SQL statements
So, you have two Delete columns? One for Sender and one for Recipeint?
Can you post the CREATE TABLE statements for threads, messages and profiles so I can put together a valid sql statement? You might not have to do the union and that might not be the answer. I just need more info to help.
Can you post the CREATE TABLE statements for threads, messages and profiles so I can put together a valid sql statement? You might not have to do the union and that might not be the answer. I just need more info to help.
ASKER
There are a ton of tables they depend on :D
USE [ASPNETDB.MDF]
GO
/****** Object: Table [dbo].[ag_profiles] Script Date: 11/14/2008 15:10:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ag_profiles](
[ag_profile_id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[DateTimeStamp] [smalldatetime] NOT NULL,
[UpdateDate] [smalldatetime] NULL,
[IPAddress] [char](15) NULL,
[Opt_Out] [bit] NOT NULL,
[ProfileName] [nvarchar](25) NULL,
[ProfilePic] [varbinary](max) NULL,
[ProfileThumb] [varbinary](max) NULL,
[ProfilePicMIME] [varchar](50) NULL,
[SkateStance] [nvarchar](7) NULL,
[FavTricks] [nvarchar](100) NULL,
[FavSpots] [nvarchar](100) NULL,
[FavSkaters] [nvarchar](100) NULL,
[FavVideoparts] [nvarchar](100) NULL,
[FavSongs] [nvarchar](100) NULL,
[Location] [nvarchar](50) NULL,
[Setup] [nvarchar](100) NULL,
[RealName] [nvarchar](100) NULL,
[SkateStyle] [nvarchar](100) NULL,
[LocalHeros] [nvarchar](100) NULL,
[Age] [int] NULL,
[AboutMe] [nvarchar](500) NULL,
CONSTRAINT [PK_ag_profiles] PRIMARY KEY CLUSTERED
(
[ag_profile_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ag_profiles] WITH CHECK ADD CONSTRAINT [FK_ag_profiles_asp_users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[ag_profiles] CHECK CONSTRAINT [FK_ag_profiles_asp_users]
GO
ALTER TABLE [dbo].[ag_profiles] ADD CONSTRAINT [DF_ag_profiles_DateTimeStamp] DEFAULT (((1)/(1))/(2000)) FOR [DateTimeStamp]
GO
ALTER TABLE [dbo].[ag_profiles] ADD CONSTRAINT [DF_ag_profiles_Opt_Out] DEFAULT ((0)) FOR [Opt_Out]
GO
USE [ASPNETDB.MDF]
GO
/****** Object: Table [dbo].[ag_threads] Script Date: 11/14/2008 15:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ag_threads](
[ThreadId] [uniqueidentifier] NOT NULL,
[SenderId] [uniqueidentifier] NOT NULL,
[RecipientId] [uniqueidentifier] NOT NULL,
[SenderDelete] [bit] NOT NULL,
[RecipientDelete] [bit] NOT NULL,
[Subject] [nvarchar](75) NOT NULL,
CONSTRAINT [PK_ag_threads] PRIMARY KEY CLUSTERED
(
[ThreadId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ag_threads] WITH CHECK ADD CONSTRAINT [FK_Threads_RecipientId_Users_UserId] FOREIGN KEY([RecipientId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[ag_threads] CHECK CONSTRAINT [FK_Threads_RecipientId_Users_UserId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'RecipientId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ag_threads', @level2type=N'CONSTRAINT',@level2name=N'FK_Threads_RecipientId_Users_UserId'
GO
ALTER TABLE [dbo].[ag_threads] WITH CHECK ADD CONSTRAINT [FK_Threads_SenderId_Users_UserId] FOREIGN KEY([SenderId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[ag_threads] CHECK CONSTRAINT [FK_Threads_SenderId_Users_UserId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SenderID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ag_threads', @level2type=N'CONSTRAINT',@level2name=N'FK_Threads_SenderId_Users_UserId'
GO
ALTER TABLE [dbo].[ag_threads] ADD CONSTRAINT [DF_ag_threads_SenderDelete] DEFAULT ((0)) FOR [SenderDelete]
GO
ALTER TABLE [dbo].[ag_threads] ADD CONSTRAINT [DF_ag_threads_RecipientDelete] DEFAULT ((0)) FOR [RecipientDelete]
GO
USE [ASPNETDB.MDF]
GO
/****** Object: Table [dbo].[ag_messages] Script Date: 11/14/2008 15:08:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ag_messages](
[ThreadId] [uniqueidentifier] NOT NULL,
[MessageId] [uniqueidentifier] NOT NULL,
[SenderId] [uniqueidentifier] NOT NULL,
[RecipientId] [uniqueidentifier] NOT NULL,
[TimeSent] [smalldatetime] NOT NULL,
[TimeRead] [smalldatetime] NULL,
[Message] [nvarchar](500) NOT NULL,
CONSTRAINT [PK_ag_messages] PRIMARY KEY CLUSTERED
(
[MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ag_messages] WITH CHECK ADD CONSTRAINT [FK_Message_Users_RecipientId] FOREIGN KEY([RecipientId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[ag_messages] CHECK CONSTRAINT [FK_Message_Users_RecipientId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'RecipientId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ag_messages', @level2type=N'CONSTRAINT',@level2name=N'FK_Message_Users_RecipientId'
GO
ALTER TABLE [dbo].[ag_messages] WITH CHECK ADD CONSTRAINT [FK_Message_Users_SenderId] FOREIGN KEY([SenderId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[ag_messages] CHECK CONSTRAINT [FK_Message_Users_SenderId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Sender' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ag_messages', @level2type=N'CONSTRAINT',@level2name=N'FK_Message_Users_SenderId'
GO
ALTER TABLE [dbo].[ag_messages] WITH CHECK ADD CONSTRAINT [FK_Messages_Threads_ThreadId] FOREIGN KEY([ThreadId])
REFERENCES [dbo].[ag_threads] ([ThreadId])
GO
ALTER TABLE [dbo].[ag_messages] CHECK CONSTRAINT [FK_Messages_Threads_ThreadId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ThreadId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ag_messages', @level2type=N'CONSTRAINT',@level2name=N'FK_Messages_Threads_ThreadId'
GO
So now, when a user opens the page to see threads, it shows all of the ones they participated in either as sender or recipeint? Is that correct?
ASKER
Yes. Its like a conversation. Each person can have mulitple conversations going on, even with the same person or other people
I just wrote a query that does the complete opposite of what I wanted it to do hahaha using the UNION
it return only ones that had been deleted! :*( hahaha
I just wrote a query that does the complete opposite of what I wanted it to do hahaha using the UNION
it return only ones that had been deleted! :*( hahaha
ASKER
my brain must be half fried hahaha I just wrote this, and it only returns deleted messages by that user
SELECT ag_threads.Subject, ag_messages.ThreadId, ag_messages.MessageId, ag_messages.SenderId, ag_messages.RecipientId, ag_messages.TimeSent, ag_messages.Message, ag_profiles.RealName
FROM ag_threads
INNER JOIN (ag_profiles INNER JOIN ag_messages ON ag_profiles.[UserId] = ag_messages.[SenderId]) ON ag_threads.[ThreadId] = ag_messages.[ThreadId]
WHERE ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a INNER JOIN ag_threads b ON a.[ThreadId]= b.[ThreadId] WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER) AND b.RecipientDelete != 0)
UNION
SELECT ag_threads.Subject, ag_messages.ThreadId, ag_messages.MessageId, ag_messages.SenderId, ag_messages.RecipientId, ag_messages.TimeSent, ag_messages.Message, ag_profiles.RealName
FROM ag_threads
INNER JOIN (ag_profiles INNER JOIN ag_messages ON ag_profiles.[UserId] = ag_messages.[SenderId]) ON ag_threads.[ThreadId] = ag_messages.[ThreadId]
WHERE ag_messages.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER)
AND ag_messages.timeSent = (SELECT MAX(timesent) FROM ag_messages a INNER JOIN ag_threads b ON a.[ThreadId]= b.[ThreadId] WHERE a.threadid = ag_threads.threadid AND a.RecipientID = CAST(@UserID AS UNIQUEIDENTIFIER) AND b.SenderDelete != 0)
ORDER BY ag_messages.TimeSent DESCASKER
AND b.RecipientDelete != 0 I added that, but if i change it to this
AND b.RecipientDelete = 0 then it still returns ALL the threads !
AND b.RecipientDelete = 0 then it still returns ALL the threads !
ASKER
this is definately the hardest sql statement I have ever had to do hahahha
its crazy that a already solid sql statement would be so hard to add to.
I think maybe I have to rewrite the sql statement from the beginning and make it so first it only selects records in which the user was the original sender then union that with a complete other sql statement where that user is the original recipient, then! in each of those say to exclude the deleted ones, AND THEN Union them together.
15 minutes until I am off, I will reply tomorrow afternoon hoggzilla :) I never let a question I started die! heheheh e
its crazy that a already solid sql statement would be so hard to add to.
I think maybe I have to rewrite the sql statement from the beginning and make it so first it only selects records in which the user was the original sender then union that with a complete other sql statement where that user is the original recipient, then! in each of those say to exclude the deleted ones, AND THEN Union them together.
15 minutes until I am off, I will reply tomorrow afternoon hoggzilla :) I never let a question I started die! heheheh e
Cool, I have created the tables on a dev box and will look later tonight, I hope.
ASKER
right on! I appreciate it. I just loaded it on my dev box at home. I am going to continue to play with it.
I felt like my original query was built well enough, but I think it might need to be re-written to work for something like this.
I am going to try to take that route tonight :D
I felt like my original query was built well enough, but I think it might need to be re-written to work for something like this.
I am going to try to take that route tonight :D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How are you doing so far? I thought about this some last night. Can we define this logically and then create the stmt? Here is what you need, please confirm:
1. Based on the User, which I know, return all THREADS for which the User is the Sender
--- if the original recipent hasn't replied it shouldn't show in the return- btw it doesn't with the current
---statement
2. Also return the Recipient THREADS, but don't duplicate the ones above.
3. Only return the most recent message per THREAD.
---- would be cool if it only returned the most recent message from the other user "not the users of the inbox"
4. If the Current User has deleted this thread, could be either in the Sender or Recipient Delete box, don't show the THREAD in the current users Inbox.
--- Current user
Hahaha I wish I could explain stuff like you hahahah I am confusing when i try to talk technical, but my brain knows how to process it hahaha just not express it verbally haha
thank you! that makes things much simplier!
I am actually at the company now who I am doing the site for, they just got the building permit and are having a meeting. I will be home tonight to give this statement another crack. I will reply in the morning with my results.
thanks again :D
1. Based on the User, which I know, return all THREADS for which the User is the Sender
--- if the original recipent hasn't replied it shouldn't show in the return- btw it doesn't with the current
---statement
2. Also return the Recipient THREADS, but don't duplicate the ones above.
3. Only return the most recent message per THREAD.
---- would be cool if it only returned the most recent message from the other user "not the users of the inbox"
4. If the Current User has deleted this thread, could be either in the Sender or Recipient Delete box, don't show the THREAD in the current users Inbox.
--- Current user
Hahaha I wish I could explain stuff like you hahahah I am confusing when i try to talk technical, but my brain knows how to process it hahaha just not express it verbally haha
thank you! that makes things much simplier!
I am actually at the company now who I am doing the site for, they just got the building permit and are having a meeting. I will be home tonight to give this statement another crack. I will reply in the morning with my results.
thanks again :D
ASKER
lastnight at home.... I got this statement working in like 15 minutes hahah
I did exactly what i said i would. I re-created the first statement in a way that it first selected only threads the user had originally sent and then unioned that with threads the user had originally received.
then I went from there and it was a breeeezzeeeee :D
I don't know the performance of this query.. hahaha but its working and thats all that matters at this point :D!!!!!!!!!!!
thanks for your help !
I did exactly what i said i would. I re-created the first statement in a way that it first selected only threads the user had originally sent and then unioned that with threads the user had originally received.
then I went from there and it was a breeeezzeeeee :D
I don't know the performance of this query.. hahaha but its working and thats all that matters at this point :D!!!!!!!!!!!
thanks for your help !
ASKER
your awesome. thanks for the support
Open in new window