This is a continuation to the question posted - Stored Procedure/SQL script that allows me to add a record to a table
I have 2 tables, one with all the email data, and another with all the specific member email data that one creates a row if an email has been read. Once an email has been read by a member its added to the Member_email_read table (which is created and populated based on all read emails).
The tables are as follows:
SELECT [member_email_id]
,[member_email_FK_message_id]
,[member_email_FK_user_id]
,[member_email_status]
,[member_email_read_datetime]
,[member_email_delete_datetime]
FROM [MemberData].[dbo].[Member_email_read]
SELECT[message_id]
,[email_catlogue_num]
,[email_FK_user_id]
,[Email_time]
,[email_content]
,[Email_created_date]
FROM [MemberData].[dbo].[Email]
Based on a previous answer it was believed that the best approach was to create a MERGE which would UPDATE when condition was MATCHED and INSERT when NOT MATCHED.
MERGE [MemberData].[dbo].[Member_email_read] AS tgt
USING (
SELECT message_id, user_id, 'R', null, CURRENT_TIMESTAMP
FROM [MemberData].[dbo].[Email]
WHERE user_id = @UserId
) AS src (MessageId, UserId, Status, ReadDate, DeleteDate)
ON (
tgt.member_email_FK_message_id = src.message_id
AND tgt.member_email_FK_user_id = src.user_id
)
WHEN MATCHED THEN
UPDATE SET tgt.member_email_status = src.Status,
tgt.member_email_read_datetime = src.ReadDate,
tgt.member_email_delete_datetime = src.DeleteDate
WHEN NOT MATCHED THEN
INSERT (member_email_FK_message_id, member_email_FK_user_id,
member_email_status, member_email_read_datetime, member_email_delete_datetime)
VALUES (src.MessageId, src.UserId, src.Status, src.ReadDate, src.DeleteDate);
This works well when marking the unread emails as read for the root user (who sends most of them) however, this does not quite work for other users. What happens for other users is that the user ids from both tables are joined but the message ids aren't. This means that there are different rows created. it would seem that the root user id has (almost) all message_ids associated to them, however a normal user does not. As soon as they (a normal) reads a message then a record is created with their user id and the message Id which did not exist in in the [Email] table. Is there anyway to fix this so that it would include these records without having to many check the tickbox in the front end? Any ideas @gmiley ?
If I want to check as read all emails for user_id = 78, I enter it into the MERGE stored procedure (mentioned above) and a lot of records are set to read for user_id = 78. However if I try and run a look for a specific one from the inbox of user_id = 78 (with Email content of "Test 1"). I get this result from
If I want an email with Email content of 'Test 1' set to read the I do a select * From [dbo].[Email] Where [Email_content] = 'Test 1'. It retrieves the following information -
message_id email_catlogue_num email_FK_user_id Email_time Email_content
1806 1265 2 20/12/2017 19:52 Test 1
This shows that there is a record for user_id = 2 (root user), however this also shows in the inbox for user_id = 78. However it will not show in the table, I get nothing (added 0s to signify nothing):
member_email_id member_email_FK_message_id member_email_FK_user_id member_email_status member_email_read_datetime member_email_delete_datetime
0 0 0 0 0 0
What I am expecting is (when I am logged on as user_id =78 and read the message)
member_email_id member_email_FK_message_id member_email_FK_user_id member_email_status member_email_read_datetime member_email_delete_datetime
7035 1806 78 r 22/12/2017 14:53 9999-12-31 00:00:00.000
Email table is as follows:
+--------------------+--------------+-----------+
| message_id | int | Unchecked |
+--------------------+--------------+-----------+
| email_catlogue_num | int | Unchecked |
| email_FK_user_id | int | Unchecked |
| Email_time | datetime | Checked |
| email_content | nvarchar(10) | Checked |
| Email_created_date | datetime | Checked |
|+--------------------+--------------+-----------+
The Member_email_read table is as follows:
+------------------------------+------------+-----------+
| member_email_id | int | Unchecked |
+------------------------------+------------+-----------+
| member_email_FK_message_id | int | Unchecked |
| member_email_FK_user_id | int | Unchecked |
| member_email_status | varchar(1) | Unchecked |
| member_email_read_datetime | datetime | Checked |
| member_email_delete_datetime | datetime | Unchecked |
+------------------------------+------------+-----------+
