[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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

0
HarleySkater
Asked:
HarleySkater
  • 13
  • 6
1 Solution
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now