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!


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))

Open in new window

LVL 1
HarleySkaterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
I will be honest, hard to understand exactly what you want ... but here is a revised sql from yours above. Let's talk from this.
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]
INNER JOIN	ag_profiles ON ag_profiles.[UserId] = ag_messages.[SenderId]
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))

Open in new window

0
HarleySkaterAuthor Commented:
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
0
Steve HoggITCommented:
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]
		
    

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

HarleySkaterAuthor Commented:
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!  
0
HarleySkaterAuthor Commented:
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
0
HarleySkaterAuthor Commented:
I think I am going to try to take the UNION approach and just write 2 different SQL statements
0
Steve HoggITCommented:
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.
0
HarleySkaterAuthor Commented:
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

Open in new window

0
Steve HoggITCommented:
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?
0
HarleySkaterAuthor Commented:
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
0
HarleySkaterAuthor Commented:
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 DESC

Open in new window

0
HarleySkaterAuthor Commented:
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 !
0
HarleySkaterAuthor Commented:
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
0
Steve HoggITCommented:
Cool, I have created the tables on a dev box and will look later tonight, I hope.
0
HarleySkaterAuthor Commented:
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
0
Steve HoggITCommented:
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
2. Also return the Recipient THREADS, but don't duplicate the ones above.
3. Only return the most recent message per THREAD.
4. If the User has deleted this thread, could be either in the Sender or Recipient Delete box, don't show the THREAD.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HarleySkaterAuthor Commented:
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
0
HarleySkaterAuthor Commented:
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 !
0
HarleySkaterAuthor Commented:
your awesome.  thanks for the support
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.